Convert Range into Set for CSE use

[Trojan35]

Hi, I need to do this formula:

=SUM(IF((A1:A100={1,59,303}), B1:B100, 0))

but I need to use cells D1:D3 instead of {1,59,303}. I know i could make one long formula but given that i'll need to use different ranges (say, F3:F37) for many different formulas, that is not practical.

Thanks.

 

[Brian from Maui]

Hi, I need to do this formula:

=SUM(IF((A1:A100={1,59,303}), B1:B100, 0))

but I need to use cells D1:D3 instead of {1,59,303}. I know i could make one long formula but given that i'll need to use different ranges (say, F3:F37) for many different formulas, that is not practical.

Thanks.

Maybe this will help,

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,F1:F3,0))),B1:B10)

Where F1:F3 house your criterias.

 

[Trojan35]

Wow that's awesome. Thanks. What does the -- do?