Countifs not returning correct count

ScottyDo

New Member
Joined
Aug 23, 2012
Messages
20
Hi, I'm using:


Norwegian Win 8.1
English MS Office Professional 2016 with Norwegian keyboard


I'm trying to do a count of an item (in column H), that has a 'end-of-contract date' of 14.02.2017 (in column L), that has a 'warranty end date' of 15.02.2016 or earlier (in column J).


Not all items have a warranty end date and thus are blank in column J.


I've used this formula to find these items with blanks and it works just fine.
=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;"14.02.2017";Dump!J:J;"")


I've manually gone in and checked the returned count and it is correct.


But when I want to find an item with a warranty date less than 16.02.2016, it returns "0", when I know I have approx 2500 items with a warranty end date less than 16.02.2016.


=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;"14.02.2017";Dump!J:J;"<16.02.2016")


"Dump" is the sheet name where the data lies.


Any ideas why this doesn't work? Mind you the date format we use here is European: dd.mm.yyyy


Cheers and thx for any guidance,
Scott
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
Maybe this

=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;DATE(2017,2,14);Dump!J:J;"<"&DATE(2016,2,16))

M.
 
Last edited:

ScottyDo

New Member
Joined
Aug 23, 2012
Messages
20
Hi...thx for your reply.
This did not work. It did not execute when I copied it into the cell and hit enter.
The parentheses become highlighted in red is all that happensed.

=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;DATE(2017,2,14);Dump!J:J;"<"&DATE(2016,2,16))
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
Adjust/correct the argument separators

Try
=COUNTIFS(Dump!H:H;"SC controller";Dump!L:L;DATE(2017;2;4);Dump!J:J;"<"&DATE(2016;2;16))

M.
 
Last edited:

ScottyDo

New Member
Joined
Aug 23, 2012
Messages
20

ADVERTISEMENT

Hi,

Thx...but it returns "0".

Scott
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
Are you sure there is a row that satisfies both criteria L=DATE(2017;2;4) and J < DATE(2016;2;16)?<date(2016;2;16)?


Check if the values in columns J and L are real dates, not text.

Try
=ISNUMBER(J2)
and
=ISNUMBER(L2)

M.</date(2016;2;16)?
 
Last edited:

ScottyDo

New Member
Joined
Aug 23, 2012
Messages
20

ADVERTISEMENT

Both return a 'False' response. Both are defined as "date" in the cell format menu.
 

ScottyDo

New Member
Joined
Aug 23, 2012
Messages
20
OK, I've done this and there is no change.

I don't understand why this can handle 2 criteria using dates, but not when a third criteria is entered.
I surely appreciate your help thus far, but I think I'll have to just do this particular calculation manually.

Cheers,
Scott
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Your cells are not recognised as dates (that is what the FALSE shows). Try the text to columns method in the link Marcelo provided. There is nothing wrong with the formula.

Are you sure that your regional date separator is a full stop/period and not a forward/common slash(/)?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,934
Members
413,953
Latest member
Arthur1471

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