Forum Discussion
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
- Mark FitzgeraldIron Contributor
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 HighCopper ContributorPerfect! I knew it had a simple solution, thanks so much!