Copying SUMIF to reference sequential columns

First time poster...first time I've run into an Excel issue I haven't been able to solve!

I am using an SUMIF formula to look for any instances of specific criteria (in this case “test”) in a range of cells and to total the amount(s) found in another range. I’m starting with:

B19=SUMIF($A$3:$A$13, "*test*",$B$3:$B$13)

However, as I increase in rows, I want Excel to do SUMIF using the same range to look for criteria but referenencing a different range for the amounts. The range changes to the next adjecnt column. So, when I move to Row 20, I want Excel to look at Column C.

B20= SUMIF($A$3:$A$13, "*test*",$C$3:$C$13)

This pattern continues as the rows increase. Thus:

B21= SUMIF($A$3:$A$13, "*test*",$D$3:$D$13)
B22= SUMIF($A$3:$A$13, "*test*",$E$3:$E$13)

And so on.

Is there a way to force Excel to copy or fill this formula down so that it recognizes to look at the next column as the rows progress?

I’ve tried filling using series and I’ve attempted to use the ‘offset’ but have not found anything that works. I’m working with rows 19 through 309 so I’d rather not have to retype the formula and update the column reference each time.

I'm willing to try anything (even macros!) if necssary.

Any suggestions?

Try INDIRECT with ADDRESS nested inside.

I used this in B19 through B309 and it worked perfectly...until row 274. There through 309 i got a #REF! error. Excel Error Trace "encountered exisiting arrows or a circular reference."

All other caluclations to that point are correct.

I sort of understand the INDEX formula but not enough to fix the error myself.

Thanks for speedy responses!

Originally Posted by daddylonglegs

Try using INDEX like this in B19 copied down

=SUMIF($A$3:$A$13,"*test*",INDEX($B$3:$IV$13,0,ROWS(B$19:B19)))

The part ROWS(B$19:B19) returns 1 in that formula but when you copy down it will increment by 1 each time......and that function is in the "columns" argument for INDEX so you get the sum of column 1 [B3:B13] in B19 then column 2 [C3:C13] in B20 etc.

I used:

SUMIF($A$3:$A$13,"test",INDIRECT(ADDRESS(3,ROW()-16+1)):INDIRECT(ADDRESS(13,ROW()-18+1)))

and it copies down past there.

The INDIRECT and ADDRESS formulas did not work for me. While I did not receive errors, I found the totals were not correct after doing some spot-checking.

I wondered if the INDEX you provided had a column range limit on it. That you do makes perfect sense and is easy to fix...almost wish I would have tried that and solved my own problem.

Since I'm using B through KF, I updated the formula to extend that far and presto...SUCCESS!

Thanks so much! You've saved me hours of work, I'm sure.

Originally Posted by daddylonglegs

In general it's best to avoid INDIRECT where you can as it's a "volatile" function. Also using INDIRECT and ADDRESS slightly offends my formulaic sensibilities (sorry Bob )......ADDRESS gives you a text address and then INDIRECT converts it to a cell reference.....there's almost always a better way than going round the houses like that, i.e. INDEX in this case.

The way I wrote the formula, specifically, restricted it.......

The formula I suggested is limited by the range $B$3:$IV$13. I used IV as the last column because that's the last column in Excel 2003 (force of habit)....as you have Excel 2007 you can extend the range to column XFD or as far as you have data, e.g.

=SUMIF($A$3:$A$13,"*test*",INDEX($B$3:$XFD$13,0,ROWS(B$19:B19)))

Sorry, Daddy, didn't mean to offend you . Yours is certainly the more elegant and direct solution,