+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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.
    Attached Files Attached Files
    Last edited by esbencito; 01-08-2018 at 05:56 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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??)...
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 01-08-2018 at 05:44 AM.
    Glenn



  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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. #10
    Registered User
    Join Date
    01-05-2018
    Location
    Lexington, KY
    MS-Off Ver
    2007
    Posts
    28

    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. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    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: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  12. #12
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

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

    Quote Originally Posted by dacheeba View Post
    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. #13
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

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

    Quote Originally Posted by FlameRetired View Post
    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: copy to clipboard
    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. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

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

    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.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #16
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #18
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count the number of Unique Items in a Column for each Unique Item in another Column
    By HangMan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2015, 01:18 PM
  2. Replies: 2
    Last Post: 10-10-2013, 11:09 AM
  3. Count Number of times Column A says "X" where Column B says "Y"
    By Bellio3105 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-10-2012, 06:29 AM
  4. Replies: 8
    Last Post: 07-05-2012, 03:07 PM
  5. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  6. Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  7. How To Add "Count" as a Column Item In Pivot Table
    By fredmeister in forum Excel General
    Replies: 5
    Last Post: 09-25-2008, 09:11 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1