Summing valuesif associated date falls within a range.

[bravos111]

This has got to be fairly simple, but my brain is fried.

I have two data sets like so as an example:

12-Dec-05 $50
13-Dec-05 $60
14-Dec-05 -$35
04-Jan-06 $100
10-Jan-06 -$20

etc.

Now I have another data set which is to just be the sums of the monthly totals.

example:

Dec-05 (need to do a sumif when the data is in Dec-05)
Jan-06 (same for this month)
etc.

I can not figure out how to get sumif to work, and I'm not even sure sumif will work best or at all here?

Thanks for any help..

 

[Jon von der Heyden]

Hi,
Welcome to the board!

You need a SUMPRODUCT():

=SUMPRODUCT(--(A1:A100>=Z1),--(A1:A100<=Z2),B1:B100)

Where Col A houses the dates, B the values and
Z1 = start date
Z2 = end date

Adjust ranges to suite

Regards,
Jon

 

[Richard Schollar]

Hi Bravos111

A couple of possibilities:

Assuming data starts in A1:B5 (as per your example above - adjust ranges to suit)

=SUMPRODUCT(--(TEXT(A1:A5,"mmmyy")=TEXT(A1,"mmmyy")),B1:B5)

or using Sumif:

=SUM(SUMIF(A1:A5,{">=1/12/2005",">31/12/2005"},B1:B5)*{1,-1})

 

[bravos111]

thanks all, I used sumproduct along with dynamic named ranges and it worked perfectly.