Countif

[liampog]

Hi there

I want to be able to COUNTIF if a specific date appears in the cells C11:C511 AND if the value in the cells N11:N511 are greater than 0.

I've tried various functions but can't seem to get the desired result.

Can anyone help me?

Thanks,
Liam

 

[Erick]

Hi there

I want to be able to COUNTIF if a specific date appears in the cells C11:C511 AND if the value in the cells N11:N511 are greater than 0.

I've tried various functions but can't seem to get the desired result.

Can anyone help me?

Thanks,
Liam

Try =SUMPRODUCT(--(DATEVALUE(C11:C511)=DATAVALUE("1/1/2011"), --(N11:N511=0))

 

[liampog]

This doesn't appear to work

I get a "The formula you typed contains an error" message.

I also changed it slightly because I need the cell references to be $C$11:$C$511 and $N$11:$N$511 but it still didn't work.

 

[liampog]

I had a mess around with some formulas and came up with this:

=COUNT(AND($C$11:$C$511="01/01/2011",$N$11:$N$511>0))

Why doesn't it work? It returns a value of 1 even when the cell ranges in qusetion are completely blank.

 

[Erick]

Sorry I missed a closing bracket there.

Are you planning on having the comparison date in a cell or are you actually looking at typing the date in the actual formula (i.e. "1/1/2011")

If you are referencing it in a cell, this formula should work:
=SUMPRODUCT(--($C$11:$C$511=$A11), --($N$11:$N$511>0))
where Cell A11 contains the date reference.

 

[liampog]

Basically in the C column there will be various dates entered as "dd/mm/yyyy" standard format.

I need the formula to count the number of times that the value in the C column is 01/01/2011 (or any other date for that matter) AND that the N column is greater than 0 for that specific row with that date in it.

So for example, if 01/01/2011 appears 5 times in the C column, but for only 3 of times that 01/01/2011 appears the N column is great than 0, I want the number 3 to be returned by the formula.

I hope this makes sense

Liam

 

[T. Valko]

Basically in the C column there will be various dates entered as "dd/mm/yyyy" standard format.

I need the formula to count the number of times that the value in the C column is 01/01/2011 (or any other date for that matter) AND that the N column is greater than 0 for that specific row with that date in it.

So for example, if 01/01/2011 appears 5 times in the C column, but for only 3 of times that 01/01/2011 appears the N column is great than 0, I want the number 3 to be returned by the formula.

I hope this makes sense

Liam

What version of Excel are you using?

Erick's SUMPRODUCT formula should work (in any version of Excel):

=SUMPRODUCT(--($C$11:$C$511=$A11),--($N$11:$N$511>0))

Where A11 is the date to be counted for.

If you're using Excel 2007 or later try this one:

=COUNTIFS($C$11:$C$511,$A11,$N$11:$N$511,">0")

 

[Erick]

So if you have "1/1/2011" in cell A11, the sumproduct formula should work...let me know if it doesn't because it worked for me. It counts the number of occurences where the date in column C that is greater than the date in A11 (1/1/2011) AND where the value in column N is greater than 0.

 

[liampog]

Hi there

I think I might have confused. I am going to have a number of these formulas for each date, so one to check for 1/1/2011, one for checking 2/1/2011, etc.

I'm using Excel 2007.

I've changed the design of the spreadsheet around a bit an included the date next to the result of the formula, I then use this cell $S$13 in place of $A$11 and it now works...

I'm curious as to why this didn't work though:

=SUMPRODUCT(--($C$11:$C$511="01/01/2011"),--($N$11:$N$511>0))

Replacing $A$11 with "01/01/2011". Why does the formula need the cell reference rather than a value?

Thanks for all the help,
Liam

 

[T. Valko]

Hi there

I think I might have confused. I am going to have a number of these formulas for each date, so one to check for 1/1/2011, one for checking 2/1/2011, etc.

I'm using Excel 2007.

I've changed the design of the spreadsheet around a bit an included the date next to the result of the formula, I then use this cell $S$13 in place of $A$11 and it now works...

I'm curious as to why this didn't work though:

=SUMPRODUCT(--($C$11:$C$511="01/01/2011"),--($N$11:$N$511>0))

Replacing $A$11 with "01/01/2011". Why does the formula need the cell reference rather than a value?

Thanks for all the help,
Liam

When you quote something in a formula Excel evaluates that as a TEXT string.

So:

"01/01/2011" is evaluated as a TEXT string and not the date 1/1/2011.

You can coerce that text string to be evaluated as the date like this:

=SUMPRODUCT(--($C$11:$C$511=--"01/01/2011"),--($N$11:$N$511>0))

However, you're better off ussing a cell to hold the date and then referring to that cell.

Also, if you're using Excel 2007 and you don't need backward version compatibility then using COUNTIFS is a better choice.