Help summing a range base on a date
 

A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the
data. I need a total by month, so I need to know the total for July, August,
etc. Sumproduct works if the data is in 2 columns but not with the dates in
a column and the data in a range.

Your help is appreciated.

A1 B1 C1 D1
Date Acct 1 Acct 2 Acct 3
7/1/09 $56.00 $34.00 $130
7/10/09 $57.00 $76.00 $230
7/13/09 $95.00 $33.00 $630
8/12/09 $78.00 $0.00 $270
8/16/09 $39.00 $79.00 $830
8/31/09 $87.00 $3.00 $278

Help summing a range base on a date
 

With the query date in cell E1 try the below

=SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy")),B2:B3800+C 2:C3800+D2:D3800)

If this post helps click Yes
---------------
Jacob Skaria


"VinceW" wrote:

A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the
data. I need a total by month, so I need to know the total for July, August,
etc. Sumproduct works if the data is in 2 columns but not with the dates in
a column and the data in a range.

Your help is appreciated.

A1 B1 C1 D1
Date Acct 1 Acct 2 Acct 3
7/1/09 $56.00 $34.00 $130
7/10/09 $57.00 $76.00 $230
7/13/09 $95.00 $33.00 $630
8/12/09 $78.00 $0.00 $270
8/16/09 $39.00 $79.00 $830
8/31/09 $87.00 $3.00 $278

Help summing a range base on a date
 

Thanks for the help. That did work and I was able to modify it to work with
all of my data. Wish there was a around "B2:B3800+C2:C3800+D2:D3800". Was
ok since I only had columns B to J to include, but if I would have had
several hundred, what would have been the fix?

Thanks again!

"Jacob Skaria" wrote:

With the query date in cell E1 try the below

=SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy")),B2:B3800+C 2:C3800+D2:D3800)

If this post helps click Yes
---------------
Jacob Skaria


"VinceW" wrote:

A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the
data. I need a total by month, so I need to know the total for July, August,
etc. Sumproduct works if the data is in 2 columns but not with the dates in
a column and the data in a range.

Your help is appreciated.

A1 B1 C1 D1
Date Acct 1 Acct 2 Acct 3
7/1/09 $56.00 $34.00 $130
7/10/09 $57.00 $76.00 $230
7/13/09 $95.00 $33.00 $630
8/12/09 $78.00 $0.00 $270
8/16/09 $39.00 $79.00 $830
8/31/09 $87.00 $3.00 $278

Help summing a range base on a date
 

You could try changing it to

=SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy"))*(B2:D3800) )


"VinceW" wrote:

Thanks for the help. That did work and I was able to modify it to work with
all of my data. Wish there was a around "B2:B3800+C2:C3800+D2:D3800". Was
ok since I only had columns B to J to include, but if I would have had
several hundred, what would have been the fix?

Thanks again!

"Jacob Skaria" wrote:

With the query date in cell E1 try the below

=SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy")),B2:B3800+C 2:C3800+D2:D3800)

If this post helps click Yes
---------------
Jacob Skaria


"VinceW" wrote:

A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the
data. I need a total by month, so I need to know the total for July, August,
etc. Sumproduct works if the data is in 2 columns but not with the dates in
a column and the data in a range.

Your help is appreciated.

A1 B1 C1 D1
Date Acct 1 Acct 2 Acct 3
7/1/09 $56.00 $34.00 $130
7/10/09 $57.00 $76.00 $230
7/13/09 $95.00 $33.00 $630
8/12/09 $78.00 $0.00 $270
8/16/09 $39.00 $79.00 $830
8/31/09 $87.00 $3.00 $278