# Countif

#### liampog

##### Active Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Erick

##### Active Member
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

##### Active Member
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

##### Active Member
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

##### Active Member
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

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Active Member
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

##### Well-known Member
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.

Replies
1
Views
445
Replies
2
Views
100
Replies
4
Views
320
Replies
4
Views
421
Replies
5
Views
379

1,191,310
Messages
5,985,915
Members
439,987
Latest member
g00ber

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back