sum(if()) with large range of criteria

[FortuneSyn]

I am looking for a way to use sumifs with hundreds of criteria and was hoping that i could do so somehow using arrays.

The closest I've gotten to is this formula: =SUM(IF(A2:A9={"A","B"},B2:B9,0))

However, this requires me to manually digit all the conditions. I want to sumif based on a range of criteria, based on cell values. Any help on doing this would be appreciated.

[Jonmo1]

Try

=SUMPRODUCT(SUMIF(A2:A9,E2:E3,B2:B9))


E2 = A
E3 = B

[mcmahobt]

can you post an example workbook

[FortuneSyn]

wow impressive! that works perfectly. is there a way to combine that formula with HLOOKUP() or something? I would be looking to define the column of the sum range based on a cell value.

[Jonmo1]

Can you be more specific?
Exactly how should the sum column be determined?

This will set the sum column based on which column "some value" is found in Row 1 between column B and Z

=SUMPRODUCT(SUMIF(A2:A9,E2:E3,INDEX(B2:Z9,0,MATCH("Some Value",B1:Z1,0))))