Forum Discussion
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 do I SUM the end values of an IF formulas.
For example: A1 and B1 both contain an IF function to compile data. I want to add the ending values of cells A1 and B1. So, if the IF function returned 5 value in A1 and 15 value in B1, I want my SUM formula to return 20.
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.
6 Replies
Please ignore this post. It is not aimed at you but, rather, uses your problem formulation to explore some approaches to getting an 'array of arrays' result without Excel throwing a wobbly (complaining about nested arrays).
The following solutions use:-
1. REDUCE/HSTACK to workaround BYCOL with array output
2. MAKEARRAY and INDEX
3. MAP, with thunks to hold an array of row ranges
To my mind, each of these 'solutions' is far more complicated and difficult to build than it should be; a simple formula returning a simple array of arrays in all that is needed!
REDUCE/BYROW
= DROP( REDUCE("", weekStart, LAMBDA(acc,w, HSTACK(acc, BYROW(data, LAMBDA(d, COUNTIFS(d, ">=" & w, d, "<" & w+5) ))) )), ,1)MAKEARRAY
= MAKEARRAY(ROWS(data), 4, LAMBDA(employee,weekNumber, LET( empData, INDEX(data, employee, 0), start, INDEX(weekStart, weekNumber), COUNTIFS(empData, ">=" & start, empData, "<" & start+5) ) ))MAP/thunk
= LET( Thunkλ, LAMBDA(x,LAMBDA(x)), week, IF(ROW(data), weekStart), empϑ, BYROW(data, Thunkλ), dϑ, IF(weekStart, empϑ), MAP(week, dϑ, LAMBDA(w,ϑ, COUNTIFS(ϑ(),">=" & w, ϑ(), "<" & w+5)) ) )Any thoughts?
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)- Those cells, even if they have a formula, IF in your case, just add them as parameters to your SUM function, as you would any static value.
It will add whatever your IF function returns in those cells, whether it is 5 or 15, it will add whatever that cell gives.
I hope this helps. Here is a copy of the workbook I am trying to work on. Maybe you can do something easier. I simply want to know how many times someone used a Flex or Alter based on if there is a date listed for each person.
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.
- Thank you, that helped.
