Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT and Dates
First, Thank you for helping / looking.
I'm trying to count the number of cells that match two criteria and have looked through many many responses and tried several of the suggestions but no luck. I have this formula: SUMPRODUCT(--('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5000=$A26),--(YEAR(DATEVALUE('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000))=1900)) What it is $A$2:$A$5000=$A26 is text and $M$2:$M$5000))=1900 is a date. 1900 needs to be there because I place a 0 were I don't need to look at this cell but I need to count it so the totals work out. Can I count using text for one criteria and a date for the other? Thanks in advance for your help. Joe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT and Dates
If col M has valid dates try just using
year(rnge)=1900 -- Don Guillett SalesAid Software "Joe Gieder" wrote in message ... First, Thank you for helping / looking. I'm trying to count the number of cells that match two criteria and have looked through many many responses and tried several of the suggestions but no luck. I have this formula: SUMPRODUCT(--('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5000=$A26),--(YEAR(DATEVALUE('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000))=1900)) What it is $A$2:$A$5000=$A26 is text and $M$2:$M$5000))=1900 is a date. 1900 needs to be there because I place a 0 were I don't need to look at this cell but I need to count it so the totals work out. Can I count using text for one criteria and a date for the other? Thanks in advance for your help. Joe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT and Dates
On Jun 6, 6:30 pm, Joe Gieder
wrote: First, Thank you for helping / looking. I'm trying to count the number of cells that match two criteria and have looked through many many responses and tried several of the suggestions but no luck. I have this formula: SUMPRODUCT(--('[Spares Quotes List V22 26Apr2007.xls]All'!$A$2:$A$5000=$A26),--(YEAR(DATEVALUE('[Spares Quotes List V22 26Apr2007.xls]All'!$M$2:$M$5000))=1900)) What it is $A$2:$A$5000=$A26 is text and $M$2:$M$5000))=1900 is a date. 1900 needs to be there because I place a 0 were I don't need to look at this cell but I need to count it so the totals work out. Can I count using text for one criteria and a date for the other? Thanks in advance for your help. Joe Joe, replace the YEAR(DATEVALUE(...))=1900 with M2:M5000=0? DATEVALUE expects text and turns it into a date. Your text seems a bit unorthodox, but I don't know what values you have in M2:M5000. If they are dates then test for =0 is enough. If they are text, how do you write it? HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct between dates | Excel Worksheet Functions | |||
Sumproduct using dates | Excel Worksheet Functions | |||
sumproduct between two dates | Excel Worksheet Functions | |||
SUMPRODUCT ON DATES | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions |