| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count the mean in few date but on de date it has various data | Excel Worksheet Functions | |||
| Importing Text File Data into Excel where records span several row | Excel Discussion (Misc queries) | |||
| Count/Sum data with date entries. | Excel Worksheet Functions | |||
| Count multiple data by date | Excel Worksheet Functions | |||
| Count data entries and date problem | Excel Worksheet Functions | |||
Hybrid Mode
