Forum Discussion

Adam High's avatar
Adam High
Copper Contributor
Jun 21, 2018
Solved

Getting COUNTIF to count the same unique cell multiple times

Hi all,

 

For context, I'm attempting to total up the number of items in a given sales transaction. Each sales transaction is assigned a unique ID in Column O, with a new row for each item in the transaction. In my first attempt, I used COUNTIF to simply count the number of times the unique transaction ID appeared, which was then totaled and populated in Column P, or "Order Item Count".


Now the problem, I just discovered that if there are multiples of the same item in a transaction, only one row and one transaction ID is generated, while an additional column records the quantity of that item. For example, if Customer A purchases two Item A's and one Item B, two rows of transactions will appear, with Item A having a quantity of two and Item B with a quantity of one. Therefore, my original formula will only recognize the two rows in which the transaction ID has appeared, but will not account for quantities greater than 1.

Is there any formula that could account for this? I've attached a screenshot with my original formula for clarification and hightlighted the concerned columns. I feel like there is a simple solution to this problem, but I'm really blanking on this one. Thanks for your time everyone!

  • If you change your COUNTIF to a SUMIF it will give you the quantity for each transaction ID.  So in P2 enter:

    =SUMIF(O:O,O2,G:G)

     

2 Replies

  • If you change your COUNTIF to a SUMIF it will give you the quantity for each transaction ID.  So in P2 enter:

    =SUMIF(O:O,O2,G:G)

     

    • Adam High's avatar
      Adam High
      Copper Contributor
      Perfect! I knew it had a simple solution, thanks so much!

Resources