# COUNTIFS Returning wrong value despite values being correct

#### Cemslie87

##### New Member
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 )

### 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

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

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

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

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

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.

Replies
1
Views
76
Replies
3
Views
39
Replies
14
Views
270
Replies
4
Views
75
Replies
1
Views
121

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.

### Which adblocker are you using?

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

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