# Count "x" ONLY if corresponding item number unique in other column

1. ## Count "x" ONLY if corresponding item number unique in other column

Hi all,

Been trying to solve this one for a while...

I have a data set with two columns: column A with all item numbers and column B with the corresponding buy "quantities" of each item, where "x" represents the quantity. Though, "x" is not actually a number, but rather stands for the general decision to buy that particular item. I now want to count the number of items that have to be bought, though, sometimes there would be accidental inputs of "x" as the same item number appears multiple times and thus shouldn't be counted more than once.

I already tried to solve it with SUMPRODUCT and COUNTIF, unfortunately the formula slows down my file by 30-40 seconds as the array is relatively big. Also, I tried to use a combination of FREQUENCY, IF, MATCH, ROW but that does not seem to be accurate as it ignores some duplicate items completely rather than counting one "x" for all duplicates of that particular item. SUMPRODUCT and COUNTIF also doesn't seem to be 100% accurate, but closer to the actual number, though, it calculates very SLOW!

I have attached a sample file where I explain the criteria further. Hopefully someone can help...

Please remember that my array is relatively large so the formula needs to be as efficient as possible.

2. ## Re: Count "x" ONLY if corresponding item number unique in other column

Surely the expected answer is 7 (rows 19 and 21 should count as 1 each??)...

3. ## Re: Count "x" ONLY if corresponding item number unique in other column

Ups... you're absolutely right, the correct answer should be 7 (!), played with the "x"s too many times

4. ## Re: Count "x" ONLY if corresponding item number unique in other column

In that case, the modified formula in yellow should do the job.

5. ## Re: Count "x" ONLY if corresponding item number unique in other column

Unfortunately, this needs to work without a helper column, as I have about 100 columns with "x"s for different stores...

6. ## Re: Count "x" ONLY if corresponding item number unique in other column

Why can't you delete column K, which seems to serve no useful function? I had intended it to be INSTEAD of K rather than AS WELL AS K.

7. ## Re: Count "x" ONLY if corresponding item number unique in other column

Sorry, maybe here is the confusion, I only used column K to try the FREQUENCY, IF, MATCH, ROW formula, as it does not seem to work with "x"s, but only numbers! I do not actually want another column besides the column where I put in the "x"s.

8. ## Re: Count "x" ONLY if corresponding item number unique in other column

Try this array formula:

=SUM(INDEX(((\$J\$13:\$J\$29="x")*(\$I\$13:\$I\$29<>""))/COUNTIFS(\$J\$13:\$J\$29,\$J\$13:\$J\$29&"",\$I\$13:\$I\$29,\$I\$13:\$I\$29&""),0))

9. ## Re: Count "x" ONLY if corresponding item number unique in other column

The formula works, but it's as slow as my original SUMPRODUCT. It's the
``Please Login or Register  to view this content.``
that slows it down tremendously! I'm talking about a 40 seconds calculation time compared to 0.5 seconds without the second part of the formula. I understand it's an array and with 1000+ rows things become painfully slow sometimes, but THAT slow? Isn't there a more efficient way? It makes the file pretty much unusable!

10. ## Re: Count "x" ONLY if corresponding item number unique in other column

It might be clearer if you provide a broader example of your data set. You keep mentioning "your array", but your example doesn't have a defined range, so I am not certain what array you are referencing. Also, I do not know what other calculations you have. so when you mention the slow down for your overall calculation time, I cant determine the impact a proposed solution may have for your data set.

I'm not saying attach the actual speadsheet(you are encouraged and advised to desensitize your data), but please do broaden your example sheet so that it behaves more like the the one that you are trying to get help with, especially if you are maximize calculation times.

Finally, for clarity, does this data set get sorted,filter, or otherwise moved?

Thanks

11. ## Re: Count "x" ONLY if corresponding item number unique in other column

If I understand correctly try this array entered formula.

=SUM(IF(FREQUENCY(IF((\$J\$13:\$J\$29="x")*(\$I\$13:\$I\$29<>""),--\$I\$13:\$I\$29),--\$I\$13:\$I\$29),1))
Formula:
`Please Login or Register  to view this content.`

12. ## Re: Count "x" ONLY if corresponding item number unique in other column

Originally Posted by dacheeba
It might be clearer if you provide a broader example of your data set. You keep mentioning "your array", but your example doesn't have a defined range, so I am not certain what array you are referencing. Also, I do not know what other calculations you have. so when you mention the slow down for your overall calculation time, I cant determine the impact a proposed solution may have for your data set.

I'm not saying attach the actual speadsheet(you are encouraged and advised to desensitize your data), but please do broaden your example sheet so that it behaves more like the the one that you are trying to get help with, especially if you are maximize calculation times.

Finally, for clarity, does this data set get sorted,filter, or otherwise moved?

Thanks
It basically looks the exact same in my actual data set. Just there's about 1500 rows instead of 15 as in the example and I have about 100 columns with "x"s just next to the item number (one column per store). No other major calculations. Also, I tested the above formulas for speed with a Macro, so it's definitely the
``Please Login or Register  to view this content.``
that slows it down by about 2 orders of magnitude.

13. ## Re: Count "x" ONLY if corresponding item number unique in other column

Originally Posted by FlameRetired
If I understand correctly try this array entered formula.

=SUM(IF(FREQUENCY(IF((\$J\$13:\$J\$29="x")*(\$I\$13:\$I\$29<>""),--\$I\$13:\$I\$29),--\$I\$13:\$I\$29),1))
Formula:
`Please Login or Register  to view this content.`
I have just tested it for accuracy and speed and it's EXACTLY what I was looking for! Extremely efficient and it returns the correct value! Thanks a lot everyone!!!

14. ## Re: Count "x" ONLY if corresponding item number unique in other column

You're welcome. Thanks for the feedback.

15. ## Re: Count "x" ONLY if corresponding item number unique in other column

Glad you got sorted. However, just for fun.... would you try this entered as a normal formula, on your larger dataset?

=SUM(INDEX(((\$B\$2:\$B\$18="x")*(\$A\$2:\$A\$18<>""))/COUNTIFS(\$B\$2:\$B\$18,\$B\$2:\$B\$18&"",\$A\$2:\$A\$18,\$A\$2:\$A\$18&""),0))

16. ## Re: Count "x" ONLY if corresponding item number unique in other column

I don't think it let's you enter the INDEX formula with "*" in between the criteria without putting it into an array (it seems to automatically do that), like SUMPRODUCT which is an array even though it doesn't show "{.......}". Anyway, I have just tested it, the calculation is as slow as before. See the comparison between the SUM/IF/FREQUENCY approach and SUM/INDEX/COUNTIFS below

COUNTIFS:

Countif.PNG

FREQUENCY:

Frequency.PNG

17. ## Re: Count "x" ONLY if corresponding item number unique in other column

Mmm. OK. But you CAN enter it as a non-array formula. Excel does NOT add the {} itself. It may well still be slower... I was just looking at FR's solution and noticed that "my" formula still seemed to work when entered normally.

18. ## Re: Count "x" ONLY if corresponding item number unique in other column

Hmm.. ok! I tried both, didn't seem to make any significant difference. Both formulas from FR and you work, but the SUM/IF/FREQUENCY approach is more efficient, which is important in this particular case, as my data set is relatively large

19. ## Re: Count "x" ONLY if corresponding item number unique in other column

Fair enough so.... As I said, I was just curious to see if might have made a difference!!

There are currently 1 users browsing this thread. (0 members and 1 guests)