Search code examples
excelif-statementexcel-formulasumhardcode

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,)))))

    enter image description here