Dec 28 2023 06:23 PM
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.
Dec 28 2023 06:43 PM
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.
Dec 28 2023 07:25 PM
Dec 28 2023 09:59 PM - edited Dec 28 2023 10:08 PM
Solution@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.
Dec 29 2023 03:41 AM
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)
Dec 29 2023 05:01 AM
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?
Dec 28 2023 09:59 PM - edited Dec 28 2023 10:08 PM
Solution@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.