SUMPRODUCT with 3 conditions
Solved
tomcats
-
tomcats -
tomcats -
Hello everyone,
here is the obstacle I am encountering: in a cell of a sheet, I want to sum the values of a column D when a condition is met in each of the columns A, B, and C. (A, B, C, and D are located on a second sheet).
For example: Sum the values in D when the following conditions are met in the row: A=ok, B=1, and C=blue.
Currently, I have something like this: =SUMPRODUCT(('Sheet2'!A10:A20="ok")*('Sheet2'!B10:B20="1")*('Sheet2'!C10:C20="blue"),'Sheet2'!D10:D20)
I hope I have made myself understood, which I am not very confident about.
here is the obstacle I am encountering: in a cell of a sheet, I want to sum the values of a column D when a condition is met in each of the columns A, B, and C. (A, B, C, and D are located on a second sheet).
For example: Sum the values in D when the following conditions are met in the row: A=ok, B=1, and C=blue.
Currently, I have something like this: =SUMPRODUCT(('Sheet2'!A10:A20="ok")*('Sheet2'!B10:B20="1")*('Sheet2'!C10:C20="blue"),'Sheet2'!D10:D20)
I hope I have made myself understood, which I am not very confident about.
1 answer
Hello
you are not far off unless there is a misunderstanding:
=SUMPRODUCT(('Sheet2'!A10:A20="ok")*('Sheet2'!B10:B20="1")*('Sheet2'!C10:C20="blue")*('Sheet2'!D10:D20))
SUMPRODUCT, which is a little miracle, sums up the item that contains a field address without equality.
If you have Excel 2007 or later, you can also write:
=SUMIFS(D10:D20,A10:A20,"ok",B10:B20,1,C10:C20,"Blue")
it's a bit less resource-intensive. _(add the sheet name, I'm feeling lazy tonight)
_also works with COUNTIFS without a field address to sum
best regards
are we not the only ones to understand what we are explaining?
you are not far off unless there is a misunderstanding:
=SUMPRODUCT(('Sheet2'!A10:A20="ok")*('Sheet2'!B10:B20="1")*('Sheet2'!C10:C20="blue")*('Sheet2'!D10:D20))
SUMPRODUCT, which is a little miracle, sums up the item that contains a field address without equality.
If you have Excel 2007 or later, you can also write:
=SUMIFS(D10:D20,A10:A20,"ok",B10:B20,1,C10:C20,"Blue")
it's a bit less resource-intensive. _(add the sheet name, I'm feeling lazy tonight)
_also works with COUNTIFS without a field address to sum
best regards
are we not the only ones to understand what we are explaining?
Furthermore, I ultimately opted for your second proposal, which I didn't know about and find much more practical.
Thanks again!