Countif versus SumProduct

[Rookie_User]

I have a column of dates, there can be multiples of the same date, so I
created a summary sheet that will say for 5/6/08 there were 10, using the
countif function. However in 2007 Excel when using the function wizard it
puts something called "Extract" when clicking on the date field. However the
formula doesn't work anyway. My current formulat is
=COUNTIF(YesNo3D!E:E,Extract). Column E has all the dates dates. On my
summary column I have unique dates and next to it is this formulate to try
and count the times my unique date occur?

 

[Pete_UK]

I think Extract may be a named range which contains the unique dates
you have extracted from those in column E. You certainly get this
appearing in the named range list in earlier versions if you use
Advanced filter to generate a list of unique values. If you are more
comfortable with it, change the formula to:

=COUNTIF(YesNo3D!E:E,A1)

if your first unique date on the summary sheet is in A1, then copy
down.

Hope this helps.

Pete

 

[Rookie_User]

Your awesome!! Yes I did use the advanced filter to get the unique dates -
this didn't happen in my pre 2007 version of Excel I don't think. I will try
what you suggested but great diagnosing.

 

[Pete_UK]

Thanks for your kind words (and I don't even have XL2007!!).

Pete