Count and/or sum data within a date span.
 

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".
--
JM

Count and/or sum data within a date span.
 

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JM" wrote in message
...
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".
--
JM

Count and/or sum data within a date span.
 

Thank you sooooo much!
--
JM


"RagDyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JM" wrote in message
...
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".
--
JM

Count and/or sum data within a date span.
 

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

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"JM" wrote in message
...
Thank you sooooo much!
--
JM


"RagDyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JM" wrote in message
...
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".
--
JM

Count and/or sum data within a date span.
 

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)



"RagDyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JM" wrote in message
...
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".
--
JM

Count and/or sum data within a date span.
 

On May 4, 11:13 pm, roy.okinawa
wrote:
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)

"RagDyer" wrote:
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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JM" wrote in message
...
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".
--
JM

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