SOLVED

Iron Contributor

# SUMIFS not working with blanks.

Hi,

I try to write a formula:

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

Thank you.

12 Replies

# Re: SUMIFS not working with blanks.

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.

# Re: SUMIFS not working with blanks.

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

# Re: SUMIFS not working with blanks.

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

# Re: SUMIFS not working with blanks.

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

best response confirmed by littlevillage (Iron Contributor)
Solution

# Re: SUMIFS not working with blanks.

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

# Re: SUMIFS not working with blanks.

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

# Re: SUMIFS not working with blanks.

@L z.

In your formula, I just slightly change as below:

``=SUM((Table1[ID]=G17)*(Table1[%percen]<99%)*Table1[unit])``

It's worked correctly.

# Re: SUMIFS not working with blanks.

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%",""}))``

# Re: SUMIFS not working with blanks.

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.

# Re: SUMIFS not working with blanks.

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.

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?

# Re: SUMIFS not working with blanks.

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.

# Re: SUMIFS not working with blanks.

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

# Re: SUMIFS not working with blanks.

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