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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
The cell ref needs to be outside of the double quotes, otherwise it is just a text string
Excel Formula:
=COUNTIF(H:H,"<"&B1)
 

HomeTek

New Member
Joined
Jan 27, 2017
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
It happens to everyone at some point, especially if it is something that you don't use very often.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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
Top