Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I aggregate dates
Did anyone figure out how to do this? There is a posting from CommuterVet
that ends with a problem that countif only took the last one. I have a sales forecast that is by day,month,year, and I want to subtotal by month or put it into a pivot table and have it aggregate by month, but I can't seem to be able to do it. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I aggregate dates
Assumes dates in column A and Sales in B then following will give total for
March. Change 3 to a cell if you the month value stored in a cell =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100)) C2=3 =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100)) HTH "Johnopp" wrote: Did anyone figure out how to do this? There is a posting from CommuterVet that ends with a problem that countif only took the last one. I have a sales forecast that is by day,month,year, and I want to subtotal by month or put it into a pivot table and have it aggregate by month, but I can't seem to be able to do it. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I aggregate dates
Here is what I have:=SUMPRODUCT((MONTH(G3:G73)=G3),(H3:H73))
I have dates in column G and the product amounts in column H. I am getting a zero with no message. The date format in G3 is month/year under "datevalue" as I had to convert a text date. Do I need to have a month number only or a month? I got the file from Microsoft CRM. "Toppers" wrote: Assumes dates in column A and Sales in B then following will give total for March. Change 3 to a cell if you the month value stored in a cell =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100)) C2=3 =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100)) HTH "Johnopp" wrote: Did anyone figure out how to do this? There is a posting from CommuterVet that ends with a problem that countif only took the last one. I have a sales forecast that is by day,month,year, and I want to subtotal by month or put it into a pivot table and have it aggregate by month, but I can't seem to be able to do it. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I aggregate dates
Try:
=SUMPRODUCT(--(MONTH(G3:G73)=Month(G3)),--(H3:H73)) "Johnopp" wrote: Here is what I have:=SUMPRODUCT((MONTH(G3:G73)=G3),(H3:H73)) I have dates in column G and the product amounts in column H. I am getting a zero with no message. The date format in G3 is month/year under "datevalue" as I had to convert a text date. Do I need to have a month number only or a month? I got the file from Microsoft CRM. "Toppers" wrote: Assumes dates in column A and Sales in B then following will give total for March. Change 3 to a cell if you the month value stored in a cell =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100)) C2=3 =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100)) HTH "Johnopp" wrote: Did anyone figure out how to do this? There is a posting from CommuterVet that ends with a problem that countif only took the last one. I have a sales forecast that is by day,month,year, and I want to subtotal by month or put it into a pivot table and have it aggregate by month, but I can't seem to be able to do it. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I aggregate dates
That worked. Thank you so much. Your quick response was great.
"Toppers" wrote: Try: =SUMPRODUCT(--(MONTH(G3:G73)=Month(G3)),--(H3:H73)) "Johnopp" wrote: Here is what I have:=SUMPRODUCT((MONTH(G3:G73)=G3),(H3:H73)) I have dates in column G and the product amounts in column H. I am getting a zero with no message. The date format in G3 is month/year under "datevalue" as I had to convert a text date. Do I need to have a month number only or a month? I got the file from Microsoft CRM. "Toppers" wrote: Assumes dates in column A and Sales in B then following will give total for March. Change 3 to a cell if you the month value stored in a cell =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100)) C2=3 =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100)) HTH "Johnopp" wrote: Did anyone figure out how to do this? There is a posting from CommuterVet that ends with a problem that countif only took the last one. I have a sales forecast that is by day,month,year, and I want to subtotal by month or put it into a pivot table and have it aggregate by month, but I can't seem to be able to do it. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I aggregate dates
If you want to do the aggregation by month in the pivot table you could just use the 'group by' pivot table functionality, or add an extra column to the data as =text(<date,"yyyy-mm") then use that field directly in the pivot table. regards.. -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=519145 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
US dates to UK?? | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |