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

**esbencito** · 01-08-2018, 05:26 AM

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.

**Glenn Kennedy** · 01-08-2018, 05:42 AM

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

**esbencito** · 01-08-2018, 05:46 AM

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

**Glenn Kennedy** · 01-08-2018, 05:48 AM

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

**esbencito** · 01-08-2018, 05:51 AM

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

**Glenn Kennedy** · 01-08-2018, 06:02 AM

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.

**esbencito** · 01-08-2018, 06:05 AM

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.

**Glenn Kennedy** · 01-08-2018, 06:26 AM

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

**esbencito** · 01-08-2018, 10:38 PM

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!

**dacheeba** · 01-08-2018, 11:11 PM

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

**FlameRetired** · 01-08-2018, 11:12 PM

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.

**esbencito** · 01-08-2018, 11:31 PM

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.

**esbencito** · 01-08-2018, 11:33 PM

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

**FlameRetired** · 01-09-2018, 12:01 AM

You're welcome. Thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Glenn Kennedy** · 01-09-2018, 05:04 AM

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

**esbencito** · 01-09-2018, 05:18 AM

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

**Glenn Kennedy** · 01-09-2018, 05:42 AM

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.

**esbencito** · 01-09-2018, 05:46 AM

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

**Glenn Kennedy** · 01-09-2018, 05:48 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count the number of Unique Items in a Column for each Unique Item in another Column

Count the number of text string Occurence if the Value in another Column is "Adair"

Count Number of times Column A says "X" where Column B says "Y"

formula to count number of times the letter "x" appears in a column

[SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row

Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"

How To Add "Count" as a Column Item In Pivot Table

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions