Countif not working for dates?

HomeTek

New Member
Joined
Jan 27, 2017
Messages
42
Office Version
  1. 365
Platform
  1. Windows
Hello. My problem is part of a more complicated Countifs formula but I have simplified it for the purposes of solving the part I am having issues with and posting it on here.

All I want to do is to count the number of cells in column H that contain a date less than the date in B1.

The formula works perfectly fine as it is in the screen print, but if I replace the static date with B1 with something such as...

=COUNTIF(H:H,"<B1")

Then the count no longer works.

The above is just an example, I have tried many different variations but I just can't get it to work with cell B1 as part of the formula.

Any help would be appreciated. Thank you.

image001.png
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
The cell ref needs to be outside of the double quotes, otherwise it is just a text string
Excel Formula:
=COUNTIF(H:H,"<"&B1)
 
Upvote 0
The cell ref needs to be outside of the double quotes, otherwise it is just a text string
Excel Formula:
=COUNTIF(H:H,"<"&B1)
Well don't I feel stupid now. This is pretty basic stuff.

Many thanks. Fully appreciated.
 
Upvote 0
It happens to everyone at some point, especially if it is something that you don't use very often.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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