SUMIF with multiple criterias
Posted by Andonny on May 19, 2001 7:21 PM
Hi,
...........A........B.....C
1.....category1....1.....6
2.....category1....2.....7
3.....category2....3.....8
4.....category2....4.....9
5.....category3....5.....10
6.....category3....6.....11
7.....category3....7.....12
8.....category4....8.....13
...........A...........B..........C..........D
10.....category1..category2..category3...Formula
Formula in D10
=SUMIF(A1:A8,"category1",B1:B18)+SUMIF(A1:A18,"category1",C1:C18)
The problem I am having is that I need to include category2 and category3 into the same formula above and it gets really long. I would like to just be able to include the range A10 to C10.
Thank you very much for your help
Andonny
Posted by Andonny on May 19, 2001 7:49 PM
Hi,
Could this array formula be the answer?
=SUM((A1:A8=A10:C10)*(B1:B8))+SUM((A1:A8=A10:C10)*(C1:C8))
Posted by Aladin Akyurek on May 19, 2001 9:34 PM
Hi Andonny,
The following array formulas can serve you as models:
=SUM((ISNUMBER(MATCH(A1:A8,{"category1","category2"},0))*(B1:B8)))
=SUM((ISNUMBER(MATCH(A1:A8,{"category1","category2"},0))*((B1:B8)+(C1:C8))))
The second argument of MATCH, as you see, is a constant array that holds your criteria. You can construe different constant arrays and use the cell refs of them in the above formula.
Note. Remember hitting CONTROL+SHIFT+ENTER at the same time to enter array formulas.
Aladin
Posted by Aladin Akyurek on May 19, 2001 10:05 PM
See my first post... (NT)
