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!