Counting cells

[Joel D]

Hi there,

Let me see if I can make this question make sense. I have two colums in
excel, C and H. Column 'C' contains some cells that have a date (in the form
of mm/dd/yyyy). Column 'H' contains names. I am trying to count the number
of times a name occurs, IF there is a date in column c. (No date specificly).

Thanks for your help

Joel

 

[Mike H]

Hi,

dates in Excel are numbers formatted to be seen as dates we recognise so
provided you have no numbers other than dates in Column C this should work

=SUMPRODUCT((ISNUMBER(C1:C30)*(H1:H30="Joe Bloggs")))

If you do have numbers that aren't dates in c then post back.

Mike

 

[Joel D]

Hello,

I tried that and the #NUM! error message showed up. The cells are formatted
as "general", not number or date. Is there a way to do this with that format?

 

[Mike H]

Joel,

I've tried many things and can't generate a #NUM error with this formula.
Did you copy and paste the formula or did you retype it?

Please post exactly what yo have in the 2 columns

Mike

 

[Joel D]

I've gotten it so that the message doesn't show up, but it says 0, where
there should be a value in it.

In Column 'C' there are dates(ex. 09/30/2008) in random rows. And in Column
H, there are names, (ex, BAILEY, DANIEL)

This is what I have typed in :
=SUMPRODUCT((ISNUMBER(C1:C2000)*(H1:H2000="BAILEY, DANIEL")))

To clarify, I am trying to see how many times "BAILEY, DANIEL" occurs if
there is a date in column C

Joel

 

[Mike H]

Try this small change

=SUMPRODUCT((ISNUMBER(C1:C2000)*(UPPER(TRIM(H1:H2000))="BAILEY, DANIEL")))

Mike

 

[Mike H]

I forgot sumproduct isn't case sensitive so you don't need upper

=SUMPRODUCT((ISNUMBER(C1:C2000)*(TRIM(H1:H2000)="BAILEY, DANIEL")))

Mike