Counting cells

  • Thread starter Thread starter Joel D
  • Start date Start date
J

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
 
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
 
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?
 
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
 
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
 
Try this small change

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

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

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

Mike
 
Back
Top