Sumif and autofill

G

Grimzby

I want to use autofill to increase
=SUMIF(WEEK1!$C$7:$L$7,"100",WEEK1!$C$298:$L$298) to

=SUMIF(WEEK2!$C$7:$L$7,"100",WEEK2!$C$298:$L$298)

=SUMIF(WEEK3!$C$7:$L$7,"100",WEEK3!$C$298:$L$298) ect ect....


Thanx:)
 
B

Biff

Hi!
=SUMIF(WEEK1!$C$7:$L$7,"100",WEEK1!$C$298:$L$298)
Click to expand...

Is "100" a text value or a numeric value?

As you have it enclosed in quotes Excel will treat it as TEXT.

Try this:

=SUMIF(INDIRECT("Week"&ROW(1:1)&"!C7:L7"),100,INDIRECT("Week"&ROW(1:1)&"!C298:L298"))

Copy down as needed.

Biff
 
D

Domenic

Assuming that you want to enter your formula in B2 and copy down, try
the following formula...

=SUMIF(INDIRECT("WEEK"&ROWS($B$2:B2)&"!C7:L7"),"100",
INDIRECT("WEEK"&ROWS($B$2:B2)&"!C298:L298"))

Hope this helps!
 
G

Grimzby

Thanx....both work great:).....spent hours reading and couldnt find an
answer.
The 100 is numeric value....can it be made to autofill in increments of
1 also....

Thanx again.
 
B

Biff

Hi!

Try:

=SUMIF(INDIRECT("Week"&ROW(1:1)&"!C7:L7"),100+ROWS($1:1)-1,INDIRECT("Week"&ROW(1:1)&"!C298:L298"))

Biff
 
Top