Sumif Dynamic Range

[robocop1906]

I'd like for the AD:AO range to expand with in that column range and only if a "1" has been entered in row 1. So AD= Jan and AQ= Dec, as I enter 1's in row 1 to capture YTD I'd like for the formula to gradually sum until it's at AO which is December

Help please, thanks~

=SUMIF('Labor Forecast'!M:M,'CIO Summary'!A:A,'Labor Forecast'!AD:AO)

 

[T. Valko]

I'd like for the AD:AO range to expand with in that column range and only if a "1" has been entered in row 1. So AD= Jan and AQ= Dec, as I enter 1's in row 1 to capture YTD I'd like for the formula to gradually sum until it's at AO which is December

Help please, thanks~

=SUMIF('Labor Forecast'!M:M,'CIO Summary'!A:A,'Labor Forecast'!AD:AO)

You can't use SUMIF to do that.

Try this...

=SUMPRODUCT(('Labor Forecast'!M2:M100='CIO Summary'!A2)*('Labor Forecast'!AD1:AO1=1)*'Labor Forecast'!AD2:AO100)

Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007.

 

[robocop1906]

That did not work. I'm using excel 2007 so I tried the below.

=SUMPRODUCT(('Labor Forecast'!M:M='CIL Summary'!A:A)*('Labor Forecast'!BT1:DS1=1)*'Labor Forecast'!BT:DS)

 

[T. Valko]

That did not work. I'm using excel 2007 so I tried the below.

=SUMPRODUCT(('Labor Forecast'!M:M='CIL Summary'!A:A)*('Labor Forecast'!BT1:DS1=1)*'Labor Forecast'!BT:DS)

You need to be more specific about what "did not work" means.

Did you get an error? An incorrect result?

I would not use entire columns as range references even if you're Excel 2007 or later (if you don't have data in all 1 million+ rows).

Typically, you would be doing a "one cell to many" comparison like this:

'Labor Forecast'!M:M='CIL Summary'!A1

Which breaks out as:

'Labor Forecast'!M1='CIL Summary'!A1
'Labor Forecast'!M2='CIL Summary'!A1
'Labor Forecast'!M3='CIL Summary'!A1
'Labor Forecast'!M4='CIL Summary'!A1
'Labor Forecast'!M5='CIL Summary'!A1
etc
etc

But, you're doing a one to one comparison.

'Labor Forecast'!M:M='CIL Summary'!A:A

Which breaks out as:

'Labor Forecast'!M1='CIL Summary'!A1
'Labor Forecast'!M2='CIL Summary'!A2
'Labor Forecast'!M3='CIL Summary'!A3
'Labor Forecast'!M4='CIL Summary'!A4
'Labor Forecast'!M5='CIL Summary'!A5
etc
etc

Is that really your intention?

Also, if there is any TEXT in the range 'Labor Forecast'!BT:DS that will cause an error.

 

[robocop1906]

Your second assumption is correct.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Not sure Sumproduct is the right solution. I tried it with sumif per my original post but could not get it to work.<o></o>

 

[Calicarrie]

If you can range name the area that you want to sum up (all the data without headers or line item descriptions), then you can do a sum index formula.

In this formula, if I have given my data (from cell AD2:AO100) the range name "data".

You will need to number the rows: row 2 would be referenced as row 1 (range name "first_row") and row 100 would be referenced as row 99 (range name "last_row").

Finally, range name your YTD month reference cell as "mo_num"

The formula would read like this: =SUM(INDEX(data,first_row,1):INDEX(data,last_row,mo_num))

This will give you a YTD value. It has further flexibility if you want to add up details by line item, then you would just change the first & last row references to refer to the specific line you want to add up.

Hope this helps.

 

[T. Valko]

Your second assumption is correct.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Not sure Sumproduct is the right solution. I tried it with sumif per my original post but could not get it to work.<o></o>

OK, this demonstrates what you want to do...

Book1
	A	B	C	D	E	F	G	H
1			1	2	1	5	3	1
2	A	A	58	62	90	12	37	76
3	B	G	41	96	90	77	79	24
4	H	H	48	53	33	84	43	47
5	D	F	60	52	83	85	48	36
6	K	G	30	22	78	28	60	23
7	B	C	32	55	31	1	87	3
8	B	B	27	64	12	42	17	88
9	A	D	39	61	38	51	66	24
10	G	E	60	85	38	76	18	67
Sheet1

=SUMPRODUCT((A2:A10=B2:B10)*(C1:H1=1)*C2:H10)

Returns the correct result of 479.

Don't use entire columns as range refernces. Use specifc ranges. If there is any TEXT within the sum range of C2:H10 then you'll get an error using the above formula syntax.

This formula syntax will ignore any text entries within the sum range:

=SUMPRODUCT((A2:A10=B2:B10)*(C1:H1=1),C2:H10)