# Convert Range into Set for CSE use

#### Trojan35

##### New Member
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.

### 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
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.

#### Trojan35

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

Replies
4
Views
69
Replies
28
Views
232
Replies
2
Views
175
Replies
4
Views
142
Replies
6
Views
100