COUNTIF not equal to argue not returning expected value

[Burkes]

In excel 2010, I have 2 spreadsheets, the first "Daily Count Aug 2016" has the data, the second, "NEW COUNT" has a reporting chart. A cell on "New COUNT" contains the following formula:


=COUNTIFS('[Daily Count Aug 2016.xlsx]Daily Count'!$C$2:$C$2500,"<=D",'[Daily Count Aug 2016.xlsx]Daily Count'!$D$2:$D$2500,"<>L",'[Daily Count Aug 2016.xlsx]Daily Count'!$F$2:$F$2500,$A7)


What this is supposed to do is:
1. filter out all of the cells in column c that contain either C or D
2. of those, filter out all of the cells in column D that do not contain L, but does contain E, N or Y
3. of those, count the number of cells in column F that contain a date (A7)


The value returned is 79, which is incorrect. It should be 29, however the only way I can return that value is using <n instead="" of="" <="">L, which neglects the E value in the search.


Is it the order of the formula, or do I need to add additional search parameters?</n>

 

[Jonmo1]

If it's returning any value at all (other than 0 or error), then it's not a syntactical error.
The formula is doing what it's supposed to do, it's not lying to you. There ARE 79 rows that met the criteria.
But one or more of the criteria are not doing exactly what you think they are.
It will be hard to say without seeing the actual data.

The criteria that doesn't make sense to me is the "<=D"
Less than and/or greater than comparisons are usually used for numerical entries, it can't really be relied on for Text entries.
What other values might appear in column C ?

 

[rayk8301]

your # 1 confuses me....the formula you have counts all that are D and less, so why are we filtering out anything with C or D? or I'm I not reading this right?

 

[Burkes]

Yes, the C column contains the values 7, 8, C, D, L, I just need the count of how many include C and D. So I used "<=D", to count D and less (C)

 

[Burkes]

The values contained in the C column are 7,8,C,D,L. Which is why I tried to use the "<=D" criteria, as the data comes from a report and the numbers are stored as text. I based my syntax on the order you would sort the columns in order to retrieve the value I am looking for. Column C, sort for C & D, Column D, sort for E,N,Y, and Column F sort for a specific date.

 

[rayk8301]

use the AND function for criteria instead of doing it your way...try that and let us know

 

[Jonmo1]

Are there any Blanks in column C, particularly Formulas that return "" as a blank ?
Those will be included in the count of <=D

 

[Jonmo1]

Ah this is it..

as the data comes from a report and the numbers are stored as text.

The numbers are stored as TEXT
Numbers as a TEXT ARE in fact < D<d

So you need to specifically test for C or D
Standby.</d

 

[Jonmo1]

Try

=SUM(COUNTIFS('[Daily Count Aug 2016.xlsx]Daily Count'!$C$2:$C$2500,{"C","D"},'[Daily Count Aug 2016.xlsx]Daily Count'!$D$2:$D$2500,"<>L",'[Daily Count Aug 2016.xlsx]Daily Count'!$F$2:$F$2500,$A7))

 

[Burkes]

Jonmo1 that did the trick, thank you! This had me stumped.