Help with multi sheet sumif please

[nzkaren]

I am trying to use a formula in this format in Excel 2007: =SUMPRODUCT(SUMIF(INDIRECT(I10:I11&"!D:D"),D10,INDIRECT(I10:I11&"!G:G")))

When copying this across columns I was trying to get G:G to increase to H:H, I:I...

However, it stays with G:G. If you could help, big thanks!

 

[Jonmo1]

Welcome to the board...

Try

=SUMPRODUCT(SUMIF(INDIRECT($I$10:$I$11&"!D:D"),$D$10,INDIRECT($I$10:$I$11&"!"&SUBSTITUTE(ADDRESS(1,COLUMNS($A1:G1),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,COLUMNS($A1:G1),4),1,""))))

 

[nzkaren]

Thanks! Tried that, but didn't work. Maybe because I have changed this formula that I grabbed from another site:

Mine works off a named range, as below:
=SUMPRODUCT(SUMIF(INDIRECT("'" & CCRange & "'!$B:$B"),$B15,INDIRECT("'" & CCRange & "'!K:K")))

So want to change K:K to L:L, M:M....

The sheets that I am linking to are all different, so I am not sure if this will work? It's an accounting report with accounts in B:B and the amounts I want summed in K:K.

 

[Jonmo1]

If this works...

=SUMPRODUCT(SUMIF(INDIRECT("'" & CCRange & "'!$B:$B"),$B15,INDIRECT("'" & CCRange & "'!K:K")))

And you only want to make the K:K incriment to L:L to M:M etc..when copying ACCROSS (not down)

Then

=SUMPRODUCT(SUMIF(INDIRECT("'" & CCRange & "'!$B:$B"),$B15,INDIRECT("'" & CCRange & "'!"&SUBSTITUTE(ADDRESS(1,COLUMNS($A1:K1),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,COLUMNS($A1:K1),4),1,""))))

 

[nzkaren]

Fabulous! I don't quite understand the formula yet, but it works.

 

[Jonmo1]

Here is the meat of the formula that is different from your posted formula..

SUBSTITUTE(ADDRESS(1,COLUMNS($A1:K1),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,COLUMNS($A1:K1),4),1,"")

That is in place of K:K

COLUMNS($A1:K1) - this returns the count of columns in the referenced Range..11
And it will incriment to COLUMNS($A1:L1) as filled right...12..

SUBSTITUTE(ADDRESS(1,11,4),1,"")&":"&SUBSTITUTE(ADDRESS(1,11,4),1,"")

ADDRESS(1,11,4) - returns an address string, ("K1")
The 1 = row, 11 = column, 4 means Relative reference ("K1" instead of "$K$1")

SUBSTITUTE("K1",1,"")&":"&SUBSTITUTE("K1",1,"")

SUBSTITUTE("K1",1,"") - replaces the 1 in "K1" with a blank "" = "K"

"K"&":"&"K" = "K:K"


Hope that helps.

 

[nzkaren]

Wow! Yes it does, big thanks!