Forum Discussion
SUMIFS not working with blanks.
Hi,
I try to write a formula:
=SUMIFS(Table1[unit],Table1[ID],G17,Table1[%percen],"<99%")Hope for your response,
Thank you.
If you run 2021 or 365, in H17:
=SUM((Table1[ID]=G17)*(Table1[%percen]<99%))With other versions, to avoid validating the above as an array formula (Ctrl+Shift+Enter on Windows), in I17:
=SUMPRODUCT(--(Table1[ID]=G17), --(Table1[%percen]<99%))
I hadn't realised that SUMIFS did not recognise a blank cell as a 0 for the purposes of testing a numeric criterion. Since it doesn't, the following tests both against the numeric "<99%" and equals ""
=LET( distinctID, UNIQUE(Table1[ID]), unitSums, SUMIFS(Table1[unit], Table1[ID], distinctID, Table1[%person], {"<99%", ""}), unitSum, BYROW(unitSums, SumĪ»), HSTACK(distinctID, unitSum) )and then combines the result. The first and last rows calculate the distinct IDs and finally, stacks them with the numeric results. The SUMIFS returns two columns by lifting the criterion array. These could be combined using MMULT but I have chosen to us BYROW and a lambda.ised version of the SUM function instead.
SumĪ» = LAMBDA(x, SUM(x))I know I'm a little late to the game, but I did eventually get around to this question and something about your answer (and probably the others) bothered me a bit. Trying to solve it, brought up a bunch of new complications.
First, if you don't mind, let's change the table a bit to simplify the presentation and clarify the issues:
Note that in the highlighted rows include there is now one ID (A) which appears twice, in both cases with blank %percen columns, as well as another one (C) which appears in two rows, one with a blank %percen column and the other with one below 99%.Finally, let's say we want to exclude the rows with blank %percen from the calculations. In that case, A should show 0, not 8, units, while C should show 20 units, not 23.
Using your function as-is, gives the two wrong answers indicated above.
The right answers show up if we change the sumifs() final condition from
{"<99%",""}to
{"<99%","=0"}or to
{"<99%","<0"}Out of curiosity, I also tried changing it to
{"<99%",">0"}and now the correct answer for A comes up, but the answers for all the others are wrong, particularly B who shows up with 40(!) units.
And finally, changing it to
{"<99%","<>0"}brings C up to 43 units...
Aside from the bizarre requirement that we surround the logical conditions with quote marks, why is the correct answer correct? Why are other answer so spectacularly wrong?
My intentions when writing the formula were
(i) to base it on the SUMIFS function
(ii) to include blanks as if they were 0%.
That might or might not be the OP's requirement, but that was how I read it.
As for "the bizarre requirement that we surround the logical conditions with quote marks, why is the correct answer correct?", I too found the idea of presenting a logical condition as text somewhat strange and was not greatly attracted to the syntax originally. The key is to recognise that the text criterion value combines with a range in the previous argument to give a formula which the Excel calc. engine then evaluates.
An advantage of this is that each criterion value is evaluated individually, making it possible to calculate an array of results. An extreme example of this is
= COUNTIFS(list, list)which returns a count for each item within the list.
Criterion values such as "", "=", "<>", "<", ">" read somewhat oddly but, in each case, one is comparing contents of the range against the null string. By convention, the null string appears to come between numbers and non-numeric characters.
Thank you very much.
I'm not sure i can fully understand functions that you used, but i feel you try to combine different functions so i can keep my approach. That's sumifs.
Regards,
Tuan.
If you run 2021 or 365, in H17:
=SUM((Table1[ID]=G17)*(Table1[%percen]<99%))With other versions, to avoid validating the above as an array formula (Ctrl+Shift+Enter on Windows), in I17:
=SUMPRODUCT(--(Table1[ID]=G17), --(Table1[%percen]<99%))Thank you for your response.
In your formula, I just slightly change as below:
=SUM((Table1[ID]=G17)*(Table1[%percen]<99%)*Table1[unit])It's worked correctly.
Makes sense re. what you ultimately expect to acheive
Note PeterBartholomew1's SUMIFS approach that you can adapt to Excel 2021:
=SUM(SUMIFS(Table1[unit], Table1[ID], G17, Table1[%percen], {"<99%",""}))
Excel doesn't include the two rows with an empty percentage in the result. If you enter 0% in D19 and D20, the formula will return 1 in H17 and H18.
- I have not tried myself but if you write "...,Table1[%percen]*1,..." all may be well.
