Count and/or sum data within a date span.

[Guest]

Need help with this function - cannot get it to work.
All records in the file have a purchase transaction date and a purchase
amount. I want to, for example, get a count of all transactions for the
month of August 2007. I also want to total the amounts for that month. The
data in the DATE column is entered as "xx/xx/xxxx".

 

[RagDyer]

Since you didn't give any indication of ranges,
Say purchase date is in Column B, from B2 to B100,
Amounts are in Column C, from C2 to C100.

Since we don't want to have to change the formula every period, let's put
the date to start the search in D1, and the ending date of the search in D2.

Assume all dates are XL legal dates.

To count:
=Sumproduct((B2:B100>=D1)*(B2:B100<=D2)*(C2:C100<>""))

To total:
=Sumproduct((B2:B100>=D1)*(B2:B100<=D2)*C2:C100)

 

[Guest]

Thank you sooooo much!
--
JM

Since you didn't give any indication of ranges,
Say purchase date is in Column B, from B2 to B100,
Amounts are in Column C, from C2 to C100.

Since we don't want to have to change the formula every period, let's put
the date to start the search in D1, and the ending date of the search in D2.

Assume all dates are XL legal dates.

To count:
=Sumproduct((B2:B100>=D1)*(B2:B100<=D2)*(C2:C100<>""))

To total:
=Sumproduct((B2:B100>=D1)*(B2:B100<=D2)*C2:C100)

 

[RagDyeR]

You're welcome, and thank you for the feed-back.

 

[roy.okinawa]

Hi,

Needed this same formula, however, it works for "Count" but not for "Total."
My totals will be a dollar amounts. Please help.

=SUMPRODUCT((H8:H2000>=AL1664)*(H8:H2000<=AL1665)*AJ8:AJ2000)

 

[Spiky]

Hi,

Needed this same formula, however, it works for "Count" but not for "Total."
My totals will be a dollar amounts. Please help.

=SUMPRODUCT((H8:H2000>=AL1664)*(H8:H2000<=AL1665)*AJ8:AJ2000)

I think you just have to change the last asterisk (*) to a comma (,).
Assuming you are trying to SUM the AJ column.