Hi,
I am trying to count the amount of times each month occurs in a column using the formula =SUMPRODUCT(--(C$3:C$6<>""),--(MONTH(C$3:C$6)=1)) with the date in the cell reading 12/01/2012, 11/02/2012, etc. However, I have cells which show 00/01/1900 and although I have blanked these cells out by formatting theses cells, they still seem to be counted - any ideas?
Hi
One way could be to add one more criterion in your formula, regarding the year.
=SUMPRODUCT(--(C$3:C$6<>""),--(MONTH(C$3:C$6)=1),--(year(c$3:c$6)=2012))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
If the cell displays 00/01/1900 then the underlying value is zero, so try excluding zeroes rather than blanks
=SUMPRODUCT((C$3:C$6>0)*(MONTH(C$3:C$6)=1))
Audere est facere
Thanks for the replies. Need to wait until I get into work to check the spreadsheet. I'll let you know how I got on. Thanks in advance.
Both formulas worked a treat. Thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks