Sum If on multiple columns with range of criteria
Try this...
Use cells to hold your criteria...
G2 = some location code
H2:H5 = lookup part numbers = 123, 858, 797, 485
=SUMPRODUCT(--(A2:A15=G2),--(ISNUMBER(MATCH(B2:B15,H2:H5,0))),C2:C15)
--
Biff
Microsoft Excel MVP
"44judester" wrote in message
...
I'm sure this isn't hard. Brain won't cooperate today. Going home. Had
enough.
Column A is text (Location code)
Column B is numeric (part number)
Column C is numeric (qty)
How do I sum Col C for all items in Column A that match one criterium (say
letter "A") and Column B match a range of criteria (Say p/n 123, 858, 797
and
485). DSUM works great if I had only one set of criteria and could put
them
next to each other but the report layout requires me keep the criteria
section in a limited number of columns. So, I want LOC code = "A" for all
the categories of part numbers. I need a sum of qty for each category. I
have 8 columns available and 6 different criteria:
col1 col2 col3 col4 col5 col6
col7 col8
LOC PN PN PN PN PN
PN
A 123 334 556 778 850
990
157 482 661 790 889
995
192 498 698 898
275 699
298
I don't have room to put a LOC criteria code next to each PN criteria
list.
|