Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
I'm currently using the formula below to breake it down by month but I also
need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
Hi
SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... I'm currently using the formula below to breake it down by month but I also need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
This keeps returning zero... does the date in column A have to be in the
format "yyyy.mm"? "Arvi Laanemets" wrote: Hi SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... I'm currently using the formula below to breake it down by month but I also need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
SUMPRODUCT(--(YEAR(Sheet!A1:A10)=2005),(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B
1:B10=Sheet1!C1:C10),Sheet1B1:B10) is an alternative -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Aaron Saulisberry" wrote in message ... This keeps returning zero... does the date in column A have to be in the format "yyyy.mm"? "Arvi Laanemets" wrote: Hi SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1 !C1:C10),Sheet1B1:B10) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... I'm currently using the formula below to breake it down by month but I also need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1 :B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
Hi
"Aaron Saulisberry" wrote in message ... This keeps returning zero... does the date in column A have to be in the format "yyyy.mm"? No. It simply must be in any valid date format. I just checked to be sure, and it even worked when instead dates in column A were date strings (column A formatted as text). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Arvi Laanemets" wrote: Hi SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... I'm currently using the formula below to breake it down by month but I also need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
PS. To check, is a date in valid format, change the cell format to General -
the date must be displayed as number now (p.e. todays date will be displayed as 38741) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... This keeps returning zero... does the date in column A have to be in the format "yyyy.mm"? "Arvi Laanemets" wrote: Hi SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... I'm currently using the formula below to breake it down by month but I also need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT breakdown by date
I need to be a little more patient when hitting the reply button... not even
two minutes after I posted my reply I figured it out. Thanks for the help everybody... greatly appriciated. "Arvi Laanemets" wrote: PS. To check, is a date in valid format, change the cell format to General - the date must be displayed as number now (p.e. todays date will be displayed as 38741) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... This keeps returning zero... does the date in column A have to be in the format "yyyy.mm"? "Arvi Laanemets" wrote: Hi SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Aaron Saulisberry" wrote in message ... I'm currently using the formula below to breake it down by month but I also need to have it breakdown those months by year. SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10) This formula compares two columns and if those columns have the same data for the same month then it returns the sum of the data. The only problem is is does not distingush between year, which I need it to do so. Any help is appreciated. 1-JAN-05 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Using sumproduct to count number by date | Excel Worksheet Functions | |||
Date Math Problem | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |