Sum If on multiple columns with range of criteria

[44judester]

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.

 

[T. Valko]

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)