[SOLVED] Sum all cells for which corresponding cell is not a formula and doesn't return #VALUE AND date occurs in the past

Sum all cells for which corresponding cell is not a formula and doesn't return #VALUE AND date occurs in the past

I'm trying to sum values in a row only if they are hard-coded and ignore cells with formulas. After some searching around, I found the answer in this post which seems close to what I'm looking for.

However, upon doing some testing, it seems this works for simple formulas, however the formulas I'm trying to ignore are IF statements that evaluate to #VALUE errors when I try to use the formula in the linked post. Any suggestions on how to modify this formula to ignore the #VALUE error?

I'd also like the formula to ignore any hardcoded values in the same column as a date in the future. In the example below, all "41,667" cells are formulas and should be ignored. The "25" under 5/25/2024 is hardcoded and should be counted. However, the "50,000" under 7/25/2024 should NOT be counted since that date is in the future. Is it possible to use the TODAY function to only add hardcoded numbers in the past?

Expected Result	4/25/2024	5/25/2024	6/25/2024	7/25/2024	8/25/2024
25	41,667	25	41,667	50,000	41,667

Any assistance is greatly appreciated!!

Solution

Conditional Sum

Inside a 'SUM(PRODUCT)/IFERROR combo', multiply your numbers (B5:M5) with the required conditions.
The order of the multiplicands is irrelevant.
=IFERROR(...,) is the same as =IFERROR(...,0).

Legacy Excel (Copy Down)

=SUMPRODUCT(IFERROR(B5:M5*NOT(ISFORMULA(B5:M5))*(B$4:M$4<TODAY()),))

MS365 (Spills Down)

=LET(data,B4:M14,
    hf,TAKE(data,1)<TODAY(),
    BYROW(DROP(data,1),LAMBDA(r,SUM(IFERROR(r*NOT(ISFORMULA(r))*hf,)))))