COUNT IF equation

[ved2011]

Hello Again,

I would like to do a count if equation based on a date, originally the count if formula was

=COUNTIF(TCC!K3:K500,"0")

which was working, now I am trying to make that contingent on the date with,

=COUNTIF(--(TCC!A3:A500=DATEVALUE("5/3/2011")),(TCC!G3:G500,"0"))

but is not taking, this set up worked in two previous equations but those were COUNTA and SUMPRODUCT functions. What should i change for the Countif?

Thank you

 

[Ron Coderre]

Try this:

=SUMPRODUCT((TCC!A3:A500=--"5/3/2011")*(TCC!G3:G500=0))

Does that work for you?

 

[ved2011]

Yes that works. Thank you!!!

 

[T. Valko]

Hello Again,

I would like to do a count if equation based on a date, originally the count if formula was

=COUNTIF(TCC!K3:K500,"0")

which was working, now I am trying to make that contingent on the date with,

=COUNTIF(--(TCC!A3:A500=DATEVALUE("5/3/2011")),(TCC!G3:G500,"0"))

but is not taking, this set up worked in two previous equations but those were COUNTA and SUMPRODUCT functions. What should i change for the Countif?

Thank you

If you're using Excel 2007 or later...

It's better if you use a cells to hold the criteria:

• A1 = 5/3/2011
• B1 = 0

Then the formula becomes:

=COUNTIFS(TCC!A3:A500,A1,TCC!G3:G500,B1)

 

[Ron Coderre]

Wouldn't it be great if "Excel Version" was a required field?

 

[T. Valko]

Wouldn't it be great if "Excel Version" was a required field?

Yes!

I know I've asked about it and I'm sure many others have as well.

On a side note...

I just saw your email and I'm getting ready to look into that.