COUNTIF formula returns different answer depending on sorting of referenced data

[RPIJG]

I have formulas and data across 3 tabs.
The first tab contains the following:

No Inventory	3
Below SS, no bottles	3
< 1 mos, no bottles	56
Below SS, need to kit	1
< 1 mos, bottles avail	26
>30 days coverage	73
	162

where the formula in the numerical column look like this:
=COUNTIFS(Kits!T:T,-4,'Tracked SKU'!B:B,"X") and the final value is the sum, which is correct, there are 162 items being tracked.

however, when I change the sorting applied in the Kits tab to a different column (for a different purpose), the formulas return only 159 results, which doesn't make any sense to me. It should return the same number of total SKU no matter how I've sorted it. I tried putting the Tracked SKU on the same tab as the formula so that I could rewrite the formula as such:
=COUNTIFS(Kits!T:T,-4,I:I,"X") but in that instance it returns the completely wrong information altogether which is even more perplexing to me.

Any thoughts? While I can simply put a note in that says the file must be sorted on Kits T:T to get the correct summary, it's kind of clunky.

 

[bobsan42]

Any thoughts?

yes.

While I can simply put a note in that says the file must be sorted on Kits T:T to get the correct summary, it's kind of clunky.

This will not be correct I think.

Basically you are counting data from 2 different tables - the same size i hope.
So for each row where condition 1 and condition 2 are TRUE you count +1.
Then you sort table 1, but not Table 2 - so both conditions are no longer TRUE in the same places , so you get a different count.
If you sort Table 2 in the same way as Table 1 you will get the same count.

 

[RPIJG]

yes.

This will not be correct I think.

Basically you are counting data from 2 different tables - the same size i hope.
So for each row where condition 1 and condition 2 are TRUE you count +1.
Then you sort table 1, but not Table 2 - so both conditions are no longer TRUE in the same places , so you get a different count.
If you sort Table 2 in the same way as Table 1 you will get the same count.

Ahhh, I think I see the flaw here, I'm trying to have it only Count the item if the value is equal the first and if a product code is listed in the tracked SKU, but I'm not sure that's what my formula is actually doing. I might need a more logical formula to verify, and perhaps also, I cannot do this on a whole column reference but rather have to do it by discrete range (which I'd really rather not, but perhaps the only way?).
I need to count the values when they are equal to the first condition (-4) and then check that the SKU associated with that are on the list in the column on the Tracked SKU tab.

 

[bobsan42]

just put them all in 1 table - no other easy solution if you want mess up the sorting.

Without seeing your data it is hard to tell exactly what you're up to.
But if I understand you correctly: you need to put 1 more column in table 1 - countif the sku is in table 2.
Then your COUNTIFS will be cond1 = -4, cond2: the new column is >0.

 

[RPIJG]

I basically have 4 tabs, the Kits tab has data, a VLOOKUP to the bottles tab, and logical formulas that is manipulating data pulled from an ERP system. The bottles tab is a VLOOKUP table that contains data and is used by the Kits tab. The Tracked SKU tab is the list of "active" SKU for this tracking as there are 600+ SKU that don't all need to be visualized, the previous file version I was manually removing these lines from the corresponding Kits and Bottles tab but the act of filtering, deleting, etc was inefficient at best. The inventory status chart tab, is basically a dashboard to summarize and calculate everything that is going on behind those scenes into these quick to read results. So, all one tab is way too busy. My next thought was to put a logical formula into the Kits tab that would return TRUE if the SKU is listed on the Tracked SKU list, and then build the logic around that, so both the value and the True/false would be found in the Kits tab, does that make sense?

 

[RPIJG]

View attachment 123907

I basically have 4 tabs, the Kits tab has data, a VLOOKUP to the bottles tab, and logical formulas that is manipulating data pulled from an ERP system. The bottles tab is a VLOOKUP table that contains data and is used by the Kits tab. The Tracked SKU tab is the list of "active" SKU for this tracking as there are 600+ SKU that don't all need to be visualized, the previous file version I was manually removing these lines from the corresponding Kits and Bottles tab but the act of filtering, deleting, etc was inefficient at best. The inventory status chart tab, is basically a dashboard to summarize and calculate everything that is going on behind those scenes into these quick to read results. So, all one tab is way too busy. My next thought was to put a logical formula into the Kits tab that would return TRUE if the SKU is listed on the Tracked SKU list, and then build the logic around that, so both the value and the True/false would be found in the Kits tab, does that make sense?

I gave this a try, I added in a COUNTIF on the Kits tab, this worked out correctly, then on the Inventory Status Chart tab, I updated the formula to be =COUNTIFS(Kits!T:T,-4,Kits!X:X,1) then I filtered...still doesn't work... the total # of SKU with a value in the range is 160 SKU out of 162 on the tracked tab, but depending on the sort, this number changes all over the place and makes no sense.

 

[bobsan42]

are you sorting column X with the others?

 

[Alex Blakenburg]

Also show us the vlookup you are using.
Does it have 0 or false at the end ?

 

[RPIJG]

are you sorting column X with the others?

yes, all columns on the Kits tab are part of the sort/filter. They sort correctly (stay in sequence). All data checks out on the Kit tab regardless of the sort column applied, but the formulas on the Inventory Status Chart are returning incorrectly depending on the column sorted.

 

[RPIJG]

Also show us the vlookup you are using.
Does it have 0 or false at the end ?

the VLOOKUP column from the Kits tab looks like this:
=VLOOKUP(A2,Table1,17,FALSE) it returns a numerical value that corresponds to the stock of Bottles from the bottles tab with a specific SKU