SOLVED

SUMIFS not working with blanks.

Iron Contributor

Hi, 

I try to write a formula: 

=SUMIFS(Table1[unit],Table1[ID],G17,Table1[%percen],"<99%")

small_village_2-1671729983878.png

Hope for your response,

Thank you.

 

 

 

12 Replies

@littlevillage 

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.

@Hans Vogelaar 

That two rows, I copied from a Pivot Table as below.

small_village_0-1671749688124.png

 

I have not tried myself but if you write "...,Table1[%percen]*1,..." all may be well.

@ecovonrein 

You cannot do that - functions such as SUMIFS expect a range, not an expression,

best response confirmed by littlevillage (Iron Contributor)
Solution

Hi @littlevillage 

 

Sans titre.png

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

@littlevillage 

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

@L z.

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.

 

@littlevillage 

 

Makes sense re. what you ultimately expect to acheive

Note @Peter Bartholomew's SUMIFS approach that you can adapt to Excel 2021:

=SUM(SUMIFS(Table1[unit], Table1[ID], G17, Table1[%percen], {"<99%",""}))

 

@Peter Bartholomew 

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.

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:

Screenshot 2022-12-29 112523.png


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?

@JackTradeOne 

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.

 

I see; I didn't understand that


My intentions when writing the formula were

...

(ii) to include blanks as if they were 0%.

With that being said, I must confess I still don't understand why using

 

{"<99%","<>0"}

 

brings C up to 43 units...

 

And totally unrelated - happy new year! Thanks for your contributions to this forum - I learned a lot from them (among other things, that I still have a lot to learn).

1 best response

Accepted Solutions
best response confirmed by littlevillage (Iron Contributor)
Solution

Hi @littlevillage 

 

Sans titre.png

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

View solution in original post