Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using COUNTIF or SUMPRODUCT for Year
I have a column of dates in numeric format of dd/mm/year, and have tried two
different formulas to count the number that fall in a specific year. When I use the formula =SUMPRODUCT(--(YEAR(C4:C24)=2001)) it returns the #VALUE! error I've tried variations of the COUNTIF formula, to no avail either. Any suggestions? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using COUNTIF or SUMPRODUCT for Year
It's because you have text values in C4:C24 and YEAR will return the value
error Try =ISNUMBER(C4) copy down 20 rows and anywhere you will see FALSE there is a text string maybe you have blank cells derived from formulas like IF(A1=X,"", A1) or trailing or leading spaces -- Regards, Peo Sjoblom "PSmith" wrote in message ... I have a column of dates in numeric format of dd/mm/year, and have tried two different formulas to count the number that fall in a specific year. When I use the formula =SUMPRODUCT(--(YEAR(C4:C24)=2001)) it returns the #VALUE! error I've tried variations of the COUNTIF formula, to no avail either. Any suggestions? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using COUNTIF or SUMPRODUCT for Year
Thanks for the suggestion. Upon closer examination, I noticed not all were in
the format of dd/mm/yyyy (some were mm/dd/yyyy). thanks once again, "Peo Sjoblom" wrote: It's because you have text values in C4:C24 and YEAR will return the value error Try =ISNUMBER(C4) copy down 20 rows and anywhere you will see FALSE there is a text string maybe you have blank cells derived from formulas like IF(A1=X,"", A1) or trailing or leading spaces -- Regards, Peo Sjoblom "PSmith" wrote in message ... I have a column of dates in numeric format of dd/mm/year, and have tried two different formulas to count the number that fall in a specific year. When I use the formula =SUMPRODUCT(--(YEAR(C4:C24)=2001)) it returns the #VALUE! error I've tried variations of the COUNTIF formula, to no avail either. Any suggestions? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using COUNTIF or SUMPRODUCT for Year
What does =YEAR(C4) return (and similarly for the rest of column C)? Are
you sure that you haven't got text, rather than dates, in column C? If you have, Excel will often translate them to date if they are in the format expected by your regional settings, but if (for example) your windows regional settings are looking for mm/dd/yyyy, you'll probably get #VALUE! responses if you've got dd/mm/yyyy text entries. Similarly if you have a non-date entry, you'll get #VALUE! -- David Biddulph "PSmith" wrote in message ... I have a column of dates in numeric format of dd/mm/year, and have tried two different formulas to count the number that fall in a specific year. When I use the formula =SUMPRODUCT(--(YEAR(C4:C24)=2001)) it returns the #VALUE! error I've tried variations of the COUNTIF formula, to no avail either. Any suggestions? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif month & year | Excel Worksheet Functions | |||
sumproduct for month and year | Excel Discussion (Misc queries) | |||
sumproduct in a given year | Excel Worksheet Functions | |||
Countif by Year | Excel Discussion (Misc queries) | |||
countif number of occurences per month per year. | Excel Worksheet Functions |