AND with SUMIFS Help

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.
SUMIFS_EE.xlsx
castlerjSenior AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
als315Commented:
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
SUMIFS_EE.xlsx
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
ShumsDistinguished Expert - 2017Commented:
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.
SumIFsCastlerj_SumIFS.xlsx
0
 
castlerjSenior AnalystAuthor Commented:
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.
0
 
als315Commented:
You can see expected result in totals for column with 24410 in pivot table:
sum
0
 
als315Commented:
Two solutions were given with expected result
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.