Jun 21 2018
10:34 AM
- last edited on
Jul 31 2018
08:31 AM
by
TechCommunityAP
Jun 21 2018
10:34 AM
- last edited on
Jul 31 2018
08:31 AM
by
TechCommunityAP
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!
Jun 22 2018 03:14 AM
SolutionIf 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)
Jun 26 2018 12:22 PM
Jun 22 2018 03:14 AM
SolutionIf 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)