Sumif with an array as the criteria

[ValGlad]

Hi,

Would really appreciate if someone could help me out with a problem I'm having.

If, say, I had a formula like this: SUM(SUMIF(E1:E10,{102,1733,2927,3500},F1:F10)) The criteria is an array. Now if I wanted to use a reference instead of a hard coded array inside the formula, how would I go about it.

I tried different things something like putting the array in a separate cell and referring to it inside the formula but nothing would work.

i.e. SUM(SUMIF(E1:E10,indirect(H1),F1:F10))

The reason I use the array is that the number of criteria in the criteria field varies, from one to 11 between rows, so this solution is the simplest in my view but I can't get it to work.

The criteria looks like this
154
247,265
311
322,356,364,378,399,400
412
413,414,416
etc, etc.

Thanks in advance

[FDibbins]

could you give them a named range?

[ValGlad]

I tried but it didn't work. Plus the formula needs to be copied down, since the the summarization should be done for each criteria record, meaning that each criteria cell needs to have a separate name?

[daddylonglegs]

The criteria looks like this
154
247,265
311
322,356,364,378,399,400
412
413,414,416
etc, etc.

Are the comma separated values in the same cells or is every number in a separate cell?

[ValGlad]

To daddylonglegs


In the same cell

[daddylonglegs]

In that "criteria" range all the numbers are 3 digits - is that always true? Are the numbers in E1:E10 all 3 digit numbers too?

Assuming the criteria is in H1:H6 try this "array formula"

=SUM(ISNUMBER(SEARCH(","&E1:E10&",",","&TRANSPOSE(H1:H6)&","))*F1:F10)

confirmed with CTRL+SHIFT+ENTER

[ValGlad]

In that "criteria" range all the numbers are 3 digits - is that always true? Are the numbers in E1:E10 all 3 digit numbers too?

Assuming the criteria is in H1:H6 try this "array formula"

=SUM(ISNUMBER(SEARCH(","&E1:E10&",",","&TRANSPOSE(H1:H6)&","))*F1:F10)

confirmed with CTRL+SHIFT+ENTER

The numbers in H1:H10 could be 3 or 4 digits, the numbers in E1:E10 are the same - 3 or 4 digits, it's always a single number.

I couldn't get your formula to work:
E1:E4 F1:F4 H1:H4 (CRITERIA) Formula Result
2699 1 351,1733,2699,845 4444
351 10 351,1733,2699,845 4444
1733 100 351,1733,2699,845 4444
845 1000 351,1733,2699,845 4444

[daddylonglegs]

OK, you put the formula in 4 rows? Are you expecting one result for each row of H1:H4.......or a single result for the whole thing?