Forum Discussion

CatherineMadden's avatar
CatherineMadden
Brass Contributor
Dec 29, 2023
Solved

Sum Formulas

Hello,    I have a simple spreadsheet that I want to use the SUM function. However, the cells I want to SUM have an IF formula in them already. When I use the SUM function, it returns zero. So how ...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 29, 2023

    CatherineMadden Your IF formulas return texts rather than numbers. That's why SUM returns zero. Remove all the quotation marks surrounding the ones and zeroes and the SUM function will work.

     

    Having said that, I would recommend you to do away with those nested IF formulas all-together. The attached file contains an alternative method to achieve your goal. 

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 29, 2023

CatherineMadden 

Unsurprisingly, I agree with Riny_van_Eekelen .  Your formula returns ASCII characters 48 and 49 ("0" and "1") rather than numbers 0 and 1 so the SUM does not recognise them as numbers.

 

As Riny has also observed massive simplification is possible.  This rather depends on the version of Excel that you are using though.  I chose to use COUNTIFS rather than MATCH which allowed me to expand the formula from the first cell of a row.

 

= COUNTIFS($B3:$I3, P$2:AI$2)

 

That would be a CSE array formula with legacy Excel but could be reduced further with 365.

As an aside, it is possible to build your weekly summary table directly from the data input table without needing the day by day analysis using:

 

= COUNTIFS($B3:$I3, ">=" & weekStart, $B3:$I3, "<" & weekStart+5)

 

Resources