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 :)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
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