# Sum YTD by Code

M

#### MrAcquire

I reference a report of data by code in workbook W1.xlsx produced by another
workgroup, something like this. Note that the codes are in random order.

A B C D
1 code Jan-09 Feb-09 Mar-09
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

I need to produce a report each month in a separate workbook that will
provide a YTD sum by code. The desired month is entered in B1, e.g. Feb-09.
I need a formula for cell B2 which I can copy down column B for the other
codes that will result in the YTD sum of each code. So the result of B2 in
Jan-09 would be 200, in Feb-09 350, and in Mar-09 650.

A B
1 code Feb-09
2 a
3 b
4 c

Thank you.

M

#### MrAcquire

MrAcquire said:
I reference a report of data by code in workbook W1.xlsx produced by another
workgroup, something like this. Note that the codes are in random order.

A B C D
1 code Jan-09 Feb-09 Mar-09
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

I need to produce a report each month in a separate workbook that will
provide a YTD sum by code. The desired month is entered in B1, e.g. Feb-09.
I need a formula for cell B2 which I can copy down column B for the other
codes that will result in the YTD sum of each code. So the result of B2 in
Jan-09 would be 200, in Feb-09 350, and in Mar-09 650.

A B
1 code Feb-09
2 a
3 b
4 c

Thank you.

M

#### MrAcquire

correction...
So the result of B2 in Jan-09 would be 300, in Feb-09 6100, and in Mar-09
925. Sorry.

R

#### Roger Govier

Hi

Try
=SUMPRODUCT(([Book1]Sheet1!\$B\$1:\$M\$1<=B\$1)*
([Book1]Sheet1!\$A\$2:\$A\$20=A2)*
[Book1]Sheet1!\$B\$2:\$M\$20)

Replace [Book1] with the name of your source workbook
--
Regards
Roger Govier

MrAcquire said:
I reference a report of data by code in workbook W1.xlsx produced by
another
workgroup, something like this. Note that the codes are in random order.

A B C D
1 code Jan-09 Feb-09 Mar-09
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

I need to produce a report each month in a separate workbook that will
provide a YTD sum by code. The desired month is entered in B1, e.g.
Feb-09.
I need a formula for cell B2 which I can copy down column B for the other
codes that will result in the YTD sum of each code. So the result of B2
in
Jan-09 would be 200, in Feb-09 350, and in Mar-09 650.

A B
1 code Feb-09
2 a
3 b
4 c

Thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

M

#### MrAcquire

Thank, Roger. Your formula works perfectly because the labels in row 1 of
the source workbook are dates. Now for educational purposes (mine, mostly),
how would the formula in cell B2 of the target workbook change if the labels
in row 1 were month abbreviations (i.e. alpha) instead of dates (numeric)?
See below.

Source workbook:
A B C D
1 code Jan Feb Mar
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

Target workbook:
A B
1 code Feb
2 a
3 b
4 c

The formula in B2 of the target workbook should result in YTD sum (Jan 300,
Feb 610, Mar 925, etc).

Roger Govier said:
Hi

Try
=SUMPRODUCT(([Book1]Sheet1!\$B\$1:\$M\$1<=B\$1)*
([Book1]Sheet1!\$A\$2:\$A\$20=A2)*
[Book1]Sheet1!\$B\$2:\$M\$20)

Replace [Book1] with the name of your source workbook
--
Regards
Roger Govier

MrAcquire said:
I reference a report of data by code in workbook W1.xlsx produced by
another
workgroup, something like this. Note that the codes are in random order.

A B C D
1 code Jan-09 Feb-09 Mar-09
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

I need to produce a report each month in a separate workbook that will
provide a YTD sum by code. The desired month is entered in B1, e.g.
Feb-09.
I need a formula for cell B2 which I can copy down column B for the other
codes that will result in the YTD sum of each code. So the result of B2
in
Jan-09 would be 200, in Feb-09 350, and in Mar-09 650.

A B
1 code Feb-09
2 a
3 b
4 c

Thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

R

#### Roger Govier

Hi

One way would be to just coerce the Text to a date, and then take the Month
of that date as shown below.

=SUMPRODUCT((MONTH("1 "&[Book1]Sheet1!\$B\$1:\$M\$1&" 2010")<=MONTH("1 "&B\$1&"
2010"))*
([Book1]Sheet1!\$A\$2:\$A\$20=A2)*
[Book1]Sheet1!\$B\$2:\$M\$20)

--
Regards
Roger Govier

MrAcquire said:
Thank, Roger. Your formula works perfectly because the labels in row 1 of
the source workbook are dates. Now for educational purposes (mine,
mostly),
how would the formula in cell B2 of the target workbook change if the
labels
in row 1 were month abbreviations (i.e. alpha) instead of dates (numeric)?
See below.

Source workbook:
A B C D
1 code Jan Feb Mar
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

Target workbook:
A B
1 code Feb
2 a
3 b
4 c

The formula in B2 of the target workbook should result in YTD sum (Jan
300,
Feb 610, Mar 925, etc).

Roger Govier said:
Hi

Try
=SUMPRODUCT(([Book1]Sheet1!\$B\$1:\$M\$1<=B\$1)*
([Book1]Sheet1!\$A\$2:\$A\$20=A2)*
[Book1]Sheet1!\$B\$2:\$M\$20)

Replace [Book1] with the name of your source workbook
--
Regards
Roger Govier

MrAcquire said:
I reference a report of data by code in workbook W1.xlsx produced by
another
workgroup, something like this. Note that the codes are in random
order.

A B C D
1 code Jan-09 Feb-09 Mar-09
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

I need to produce a report each month in a separate workbook that will
provide a YTD sum by code. The desired month is entered in B1, e.g.
Feb-09.
I need a formula for cell B2 which I can copy down column B for the
other
codes that will result in the YTD sum of each code. So the result of
B2
in
Jan-09 would be 200, in Feb-09 350, and in Mar-09 650.

A B
1 code Feb-09
2 a
3 b
4 c

Thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

M

#### MrAcquire

That works and is superior to an alternative that I thought of using a CHOOSE
statement. Thanks.

Roger Govier said:
Hi

One way would be to just coerce the Text to a date, and then take the Month
of that date as shown below.

=SUMPRODUCT((MONTH("1 "&[Book1]Sheet1!\$B\$1:\$M\$1&" 2010")<=MONTH("1 "&B\$1&"
2010"))*
([Book1]Sheet1!\$A\$2:\$A\$20=A2)*
[Book1]Sheet1!\$B\$2:\$M\$20)

--
Regards
Roger Govier

MrAcquire said:
Thank, Roger. Your formula works perfectly because the labels in row 1 of
the source workbook are dates. Now for educational purposes (mine,
mostly),
how would the formula in cell B2 of the target workbook change if the
labels
in row 1 were month abbreviations (i.e. alpha) instead of dates (numeric)?
See below.

Source workbook:
A B C D
1 code Jan Feb Mar
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

Target workbook:
A B
1 code Feb
2 a
3 b
4 c

The formula in B2 of the target workbook should result in YTD sum (Jan
300,
Feb 610, Mar 925, etc).

Roger Govier said:
Hi

Try
=SUMPRODUCT(([Book1]Sheet1!\$B\$1:\$M\$1<=B\$1)*
([Book1]Sheet1!\$A\$2:\$A\$20=A2)*
[Book1]Sheet1!\$B\$2:\$M\$20)

Replace [Book1] with the name of your source workbook
--
Regards
Roger Govier

I reference a report of data by code in workbook W1.xlsx produced by
another
workgroup, something like this. Note that the codes are in random
order.

A B C D
1 code Jan-09 Feb-09 Mar-09
2 c 200 150 300
3 a 300 310 315
4 b 225 215 210

I need to produce a report each month in a separate workbook that will
provide a YTD sum by code. The desired month is entered in B1, e.g.
Feb-09.
I need a formula for cell B2 which I can copy down column B for the
other
codes that will result in the YTD sum of each code. So the result of
B2
in
Jan-09 would be 200, in Feb-09 350, and in Mar-09 650.

A B
1 code Feb-09
2 a
3 b
4 c

Thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com