Sumproduct with 42 criteria

[webstmonkey]

Hello all,

I am trying to use SUMPRODUCT with 42 different criteria.

=sumproduct((Sheet1!$A$2:$A$1000=Sheet2!A2)*(Sheet1!$C$2:$C$1000=my 42 criteria))

Column A on Sheet1 is a list of names, cell A2 on Sheet2 is one of those names I want the info for. Column C on Sheet1 contains the 42 criteria, amongst other info.

All 42 items are in the same column, say F on sheet2 for the sake of argument. I've tried making them a named list, but I haven't been able to get that to work.

I would like to avoid putting each of the criteria individually into the formula if possible.

Thank you in advance for any help and advice!

[oeldere]

I think you should look at pivot table for that kind of work.

Especialy now all the arguments are in column F (same column)

[Tony Valko]

Try it like this...

Let's assume your 42 criteria are in the range Sheet2 F2:F43.

=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=Sheet2!A2),--ISNUMBER(MATCH(Sheet1!$C$2:$C$1000,Sheet2!F2:F43,0)))

[webstmonkey]

I cannot thank you enough That worked perfectly.

[Tony Valko]

You're welcome. Thanks for the feedback!