Results 1 to 19 of 19

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

Threaded View

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

    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.

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