Sumif Help

[anum]

Hi there,

I'm using this formula =-SUMIFS(BW!$F:$F,BW!$B:$B,"Essential Health",BW!$D:$D,'By MRC & SH'!F$4,BW!$E:$E,"Result") but as I drag it down the rows, I want the BW!$F:$F to be BW!$G:$G, BW!$H:$H and so on and everything else the same. Is it possible? I've made it look at a cell that has that value but that doesnt work..
Any help appreciated!

Thanks

 

[jasonb75]

You need to create an index range for each range in your formula

=-SUMIFS(INDEX(BW!$F:$AD,0,ROWS(A$1:A1)),INDEX(BW!$B:$Z,0,ROWS(A$1:A1)),"Essential Health",INDEX(BW!$D:$AB,0,ROWS(A$1:A1)),'By MRC & SH'!F$4,INDEX(BW!$E:$AC,0,ROWS(A$1:A1)),"Result")

where A1 is the cell that the first formula is going into. This one is good to drag down 25 rows without changing anything else.

 

[maabadi]

For Change Column Names:
1. First You shouldn't Fix column Letter Name (BW!E:E Not BW!$E:$E )
2. AND Important Drag it to right NOT down.

 

[anum]

You need to create an index range for each range in your formula

=-SUMIFS(INDEX(BW!$F:$AD,0,ROWS(A$1:A1)),INDEX(BW!$B:$Z,0,ROWS(A$1:A1)),"Essential Health",INDEX(BW!$D:$AB,0,ROWS(A$1:A1)),'By MRC & SH'!F$4,INDEX(BW!$E:$AC,0,ROWS(A$1:A1)),"Result")

where A1 is the cell that the first formula is going into. This one is good to drag down 25 rows without changing anything else.

thanks but it seems to come out as zeroes - the formula is from F7 and seems to show same value as Czech

 

[maabadi]

Please Upload your file with XL2BB ADDIN or upload it to free hosting site e.g. www.dropbox.com and Input link here.
To we know what is your formula at F6 & F7.

 

[anum]

thanks but it seems to come out as zeroes - the formula is from F7 and seems to show same value as Czech
View attachment 28773

also data in the other tab looks like this

Please Upload your file with XL2BB ADDIN or upload it to free hosting site e.g. www.dropbox.com and Input link here.
To we know what is your formula at F6 & F7.

sorry dont have dropbox and the excel "addin" doesnt seem to work. the formula in F6 is =-SUMIFS(BW!$F:$F,BW!$B:$B,"Essential Health",BW!$D:$D,'By MRC & SH'!F$4,BW!$E:$E,"Result")

 

[jasonb75]

It was not clear from your original question if all of the sum and criteria ranges should move right as you drag down, or just some of them, the formula that I provided assumed all ranges in the formula should move right.

For example, =-SUMIFS(BW!$F:$F,BW!$B:$B,"Essential Health",BW!$D:$D,'By MRC & SH'!F$4,BW!$E:$E,"Result") should become =-SUMIFS(BW!$G:$G,BW!$C:$C,"Essential Health",BW!$E:$E,'By MRC & SH'!F$4,BW!$F:$F,"Result") in that all columns move right by one, if this is not what is needed then you need to make the requirement clearer.

Also, looking at your screen capture, it appears that you have not edited the formula correctly as per the note in my post

where A1 is the cell that the first formula is going into.

The first formula in your sheet is in F6 but you have edited the formula to C7. Given that you have F7 selected and the range has not increased I can only assume that you have entered the same formula rather than filling down so that it expands dynamically. In F6, the edited part of the formula should be F$6:F6, when you drag down to F7 it should change to F$6:F7 and so on.

 

[anum]

It was not clear from your original question if all of the sum and criteria ranges should move right as you drag down, or just some of them, the formula that I provided assumed all ranges in the formula should move right.

For example, =-SUMIFS(BW!$F:$F,BW!$B:$B,"Essential Health",BW!$D:$D,'By MRC & SH'!F$4,BW!$E:$E,"Result") should become =-SUMIFS(BW!$G:$G,BW!$C:$C,"Essential Health",BW!$E:$E,'By MRC & SH'!F$4,BW!$F:$F,"Result") in that all columns move right by one, if this is not what is needed then you need to make the requirement clearer.

Also, looking at your screen capture, it appears that you have not edited the formula correctly as per the note in my post

The first formula in your sheet is in F6 but you have edited the formula to C7. Given that you have F7 selected and the range has not increased I can only assume that you have entered the same formula rather than filling down so that it expands dynamically. In F6, the edited part of the formula should be F$6:F6, when you drag down to F7 it should change to F$6:F7 and so on.

Got it! thats worked perfectly! thank you so much