Dec 22 2022 09:28 AM
Hi,
I try to write a formula:
=SUMIFS(Table1[unit],Table1[ID],G17,Table1[%percen],"<99%")
Hope for your response,
Thank you.
Dec 22 2022 01:32 PM
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.
Dec 22 2022 02:56 PM
Dec 22 2022 04:55 PM
Dec 22 2022 11:55 PM
You cannot do that - functions such as SUMIFS expect a range, not an expression,
Dec 23 2022 02:17 AM
Solution
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%))
Dec 23 2022 10:00 AM
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))
Dec 25 2022 03:43 PM - edited Dec 25 2022 03:49 PM
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.
Dec 26 2022 02:00 AM
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%",""}))
Dec 27 2022 05:39 AM
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.
Dec 29 2022 08:35 AM
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?
Dec 30 2022 09:50 AM - edited Dec 30 2022 09:51 AM
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.
Dec 30 2022 03:54 PM
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).
Dec 23 2022 02:17 AM
Solution
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%))