AND with SUMIFS Help

Robert Casaletta
Robert Casaletta used Ask the Experts™
I am trying to calculate the total number of times an item is used when it is on the same invoice with 2 criteria.  First, the order numbers (column A) have to match and 2) the item number R1606 also needs to be on the same order.  I have attached an example using item numbers R1606 and 24410.  Any help would be appreciated.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't clearly understand your idea, but try to play with pivot tables. You can get same results with filtering without formulas. Look at sheet2
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
One way is as als315 provided, if you want to have formulated result, then you need to have Order Number as well, check in attached, change the Order Number from dropdown and check the count. It will display total only if all 3 criteria matches; Order Number with Items Number R1606 & 24410.
Robert CasalettaManager, Warranty Performance


Thank you both for the suggestions, but it isn't what I was looking for and perhaps I was not clear.  I am trying to sum the number of occurrence of item 24410 when it is on the same order as item R1606.  That is, the criteria is 1) the same order and 2) the item R1606 must also be on the same order.  I have over 25K records that I need to summarize for this combination; there are other combinations that I also must check.
You can see expected result in totals for column with 24410 in pivot table:
Two solutions were given with expected result

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial