COUNTIFS Returning wrong value despite values being correct

Cemslie87

New Member
Joined
Mar 25, 2015
Messages
15
Hi everyone,

This is driving me mad and no doubt an easy fix.

I have a spreadsheet where column A has numerous values, and column B has date values. I have, on another sheet, a date.

My formula looks in the range A1-A100 and i have two entries in rows 5 and 6 with dates within a 30 day range.

Its for monthly reporting of calls based on the customer who calls in.

However the following formula isn't returning the 2 that it should be and is only returning 1. It works perfectly well on every other customer name (customer2), just not this one.


=COUNTIFS('Sheet1'! $A$1:$A$100 , "*Customer1*" , 'Sheet1'! $B$1:$B$100 , ">=" & 'Sheet2'!$A$1 , "<=" &'Sheet2'!$A$1 )

Thanks for any advice :)
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
You don't have enough arguments in your formula.
COUNTIFS should ALWAYS have an even number of arguments, as the pairs are "range" and "criteria", i.e.
=COUNTIFS(range1,criteria1,range2,criteria2,range3,criteria3)

You have a criteria at the end of your function with no corresponding range.
 

Cemslie87

New Member
Joined
Mar 25, 2015
Messages
15
HI thanks for getting back to me.

I may have a typo in my example above - had to copy from a VM instead of a copy and paste. This time is a copy and paste.

Sorry if i'm completely missing the point. But i see that I have a range, and Criteria for each element.

=COUNTIFS('Call Log'!$G$5:$G$100,"*Cust1*",'Call Log'!$L$4:$L$99,">="&('DateSheet'!$K$99),'Call Log'!$L$4:$L$99,"<"&(DateSheet!'$F$98))

So i have the exact same formula with Cust2 and it returns the correct numbers. However when diagnosing, i can delete the last, of 2, entries for Cust 1 the formula doesnt change. If I delete the 1st it then changes it, and it seems to be that row 49 it is just now doing anything with. Thanks for your help.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Could it be that you are not looking at values all in the same row? In column G you are using rows 5 to 100 but in column L you are using rows 4 to 99.
 

Cemslie87

New Member
Joined
Mar 25, 2015
Messages
15

ADVERTISEMENT

Good thought. I'm not sure why, but for some reason the formula will only accept column G as a higher number than the formula part including column L. Otherwise it returns an #VALUE! if they are the exact same range.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Can you upload a copy of the file (with any sensitive data removed or disguised) to DropBox, OneDrive or similar and provide a shared link here?
 

Cemslie87

New Member
Joined
Mar 25, 2015
Messages
15

ADVERTISEMENT

Sorry, unfortunately not. It's a massive excel file which i'm (i know wrongly) using it as database. I'm in the process of attempting to convert it to a python/sql database but i'm very early in my python/sql learning. I have worked around it by duplicating the line of call information at the bottom of the list and it then captures it. so i need to redact that information in order for it to read correctly in the stats.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
Can you post a small sampling of your data on the Call Log sheet, that has records that should meet the criteria?
You can use the program mentioned in the first line of Peter's signature to post images of your Excel sheet.
Also let us know the values in K99 and F98 on your DateSheet so that we can set up the example on our side, exactly as you have it.
 

Cemslie87

New Member
Joined
Mar 25, 2015
Messages
15
Yup, i will try. It doesnt seem to be wanting to do it and keeps crashing excel... i'm reducing conditional formatting where its not needed, removing drop down lists and unnecessary information to make as slick as possible - Still crashes!. I'll get back to you when i can solve it.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
For the original question you had, if you still want help with that, you could always create a small example in a new workbook that does not have all the other stuff (i.e. conditional formatting, etc). That will allow us to focus in on that exact part of it which was giving you trouble.

So you would just need to start a new workbook, with those two sheet names, a small sampling of data, and the values in the two cells being used as criteria.
If you do that, post those details, let us know what your expected result of the formula is, and what the formula is actually returning, we can probably use that to re-create it on our side and help you work out that formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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