Sum YTD by Code

[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.

 

[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.

 

[MrAcquire]

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

 

[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

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

 

[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).

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

 

[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

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).

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

 

[MrAcquire]

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

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

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).

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