Countif Problems

RachL2711

New Member
Joined
Dec 21, 2017
Messages
3
Hi, I’m having trouble with a countif formula. I’m wanting to count numbers in column B if column A has 23:59:00. The info in column A has the date and the time, so 21-Dec-2017 23:59:00, I’ve tried the below but it’s not working, it either counts all of column B or just column A.

=COUNTIFS(A9:A200,”*23:59:00*”,B9:B200,”<>”)
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

You can't use wildcards with numbers (Dates and times are stored as numbers in Excel). You could use SUMPRODUCT like this:

=SUMPRODUCT((hour(A9:A200)=23)*(minute(A9:A200)=59)*(B9:B200<>""))
 

RachL2711

New Member
Joined
Dec 21, 2017
Messages
3
That would make sense why it isn’t working! I’ve tried the formula above though and it’s still just returning 0.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,948
Messages
5,599,016
Members
414,273
Latest member
vikas007

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