Countif

liampog

Active Member
Joined
Aug 3, 2010
Messages
308
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top