New question on how can I get the total count [Solved]

I am going to revise my question with a new sample. What I am trying to do is to count the items based on their current status. I cannot seem to “see” on how to do this.Thanks ahead for any and all suggestions.

Item Total Count.ods

PLEASE don’t remove your questions! I have just lost my answer which I was typing while you removed your question. By the way, the Q&A site philosophy is not only get answers to own questions, but also help others who have similar questions; so it’s rude to remove own questions with answers and discussion history, thus making answerers ’ lives harder (context is lost), and other users won’t see answers, too.

I’m sorry about that. I knew there was an answer and not seeing any replies I decided to ask again in a different way. I’ll just “add on” next time.

Hi

=SUMPRODUCT($B$3:$B$21=F$3;$C$3:$C$21=$E4)

See item Total Count.ods

Note: the relative/absolute references allow you to enter the formula in F4 and then copy it by drag and drop

[EDIT]

I’m really not sure I understood the new request, sorry if that’s the case… See item Total CountV2.ods

Maybe you should rethink the organization of your sheet to have columns that allow the use of a pivot table.

Regards

This is really good (for me). It allows me to add other products without having to change the formula. Thank you.

Pierre-Yves, I have a question for you. Under parts, how could I change the formula if I just wanted the following: “Lock Pin”,“5mm shaft”,“LR Lock” and “Y-Rack Pin”. In my real spreadsheet the number are really RMA numbers that are always changing. Thank you for your help.

=COUNTIFS($B$3:$B$21;"234345 Lock Pin";$C$3:$C$21;"in repair")

COUNTIFS

Thank you for your solution, Pierre-Yves gives an answer which would allow me to add new product without having to edit each added product. It is good to see the different ways that leads to a very good learning curve.