Countif

[rocket0612]

I need to count the following criteria:

B1:B3000 (column contains a persons initials) = "KH"
C1:C3000 (column contains a name) = "Karen"
F1:F3000 (column contains a date) = >= Date1 & <= Date2 (variable dates that will be input)
I1:I3000 (column contains a number) = <5

any help would be greatly appreciated

thanks

[mangesh_yadav]

B1:B3000 (column contains a persons initials) = "KH"
=COUNTIF(B1:B3000,"KH")

C1:C3000 (column contains a name) = "Karen"
=COUNTIF(C1:C3000,"Karen")

F1:F3000 (column contains a date) = >= Date1 & <= Date2 (variable dates that will be input)
=COUNTIF(F1:F3000,">Date1")-(COUNT(F1:F3000)-COUNTIF(F1:F3000,"<Date2"))

I1:I3000 (column contains a number) = <5
=COUNTIF(I1:I3000,"<5")

- Mangesh

[rocket0612]

sorry,

I meant that I needed to count the number of times all the critera would be met, so it is more like a countif((B1:B3000,"KH")&(C1:C3000,"Karen"), etc..

thanks

[mangesh_yadav]

Use:

=SUMPRODUCT(--(B1:B3000="KH"),--(C1:C3000="Karen"),--(F1:F3000>=Date1),--(F1:F3000<=Date2),--(I1:I3000<5))

please use the Date1 as DATE(YEAR,MONTH,DAY)
e.g. DATE(2005,5,18)

- Mangesh

[rocket0612]

thanks for the help mangesh, howver, after doing exactly as you have advised, the result shows a '#N/A'. Any ideas?

thanks alot

[mangesh_yadav]

can you paste the exact formula you are using...

Mangesh

[mangesh_yadav]

You could also test the sumproduct formula for each condition individually to find the offending condition. probably something like this

=SUMPRODUCT(--(B1:B3000="KH"))

=SUMPRODUCT(--(C1:C3000="Karen"))

=SUMPRODUCT(--(F1:F3000>=Date1))

and so on. They should all give some legal answers. The one which is giving the #N/A is the culprit


- Mangesh

[rocket0612]

thanks alot, the offnder was the =SUMPRODUCT(--(C1:C3000="Karen")), have taken this out and it is now working so I can work round this, thanks alot

[mangesh_yadav]

Appreciate the feedback. Thanks

Mangesh