Convert Range into Set for CSE use

Trojan35

New Member
Joined
Dec 9, 2005
Messages
2
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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Trojan35 said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,636
Members
412,334
Latest member
ExcelForLifeDontHate
Top