Unable to get COUNTIF Formula to Calculate two columns

Good Afternoon!!!!

I am working on a vehicle inventory tracker I am hoping all can help me with. I am have attached a copy of the file for your reference. I am trying to calculate the inventory that each district / site has on has on hand. I am trying to get the COUNTIF formula to use two criteria's BUT I do not want it to calculate the SUM for each criteria and double my numbers. The formula I tried to use is =COUNTIF(A3:A10,"1/1")-COUNTIF(D3:D10,"Chevrolet"). I am trying to get the formula to calculate the number of vehicle brands based on each site. In the formula above I reference "1/1" and that is the District / Site and my second reference is the make of the vehicle. I used the minus symbol between the COUTIF because I did not want it to double the quantity. I hope this all makes sense. Please reference the attachments and please ask any questions for clarification.

Screenshot 2023-04-24 123630.png

Vehicle Inventory.xlsx

Thanks for catching the trailing spaces!!! I tried the formula you sent TMS and I couldn't get it to work. Maybe I am doing something wrong??

Here is an example formula that I am currently using on a much larger tracker. =COUNTIF(E10:E275,"Text 1")+COUNTIF(E10:E275,"Text 2") . Now this formula is calculating the sum for each time Text 1 & Text 2 is listed in the same column and displays the combined quantity at the bottom of my tracker. What I am trying to do is the similar except across two different columns instead of just 1. Maybe that where the COUNTIFS() comes in that MrShorty mentioned above? I want the formula to reference the "District / Site" and calculate all the "Makes" at each site and display the quantity at the bottom next to their Site #.

Here is a clip of the formula TMS gave me.


Screenshot 2023-04-24 135917.png



This is what I am trying to get the formula to do. Obviously I inputted the data manually and I did not use a formula.

Screenshot 2023-04-24 140957.png

B14=IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")

Copy across and down

Cell B14 formula , Drag down and across

Formula:

Please Login or Register  to view this content.

Please take a look at the attached spreadsheet. I tried to copy the formulas into the cells and this is all I am getting. I might be misunderstanding or doing something wrong. I appreciate all the help!!!


Vehicle Inventory 2.xlsx

Thank you for the help AliGW. This formula worked perfectly.

I have another question I am hoping someone will be able to help with. I am going to break down how I am understanding a portion of this formula thus will lead to my next question.

Formula:

=IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")

The portions of the formula =TRIM(SUBSTITUTE($A14,"Site","") is essential taking out "Site" from the cell and leaving the Site number and in this case its reading it as 1 instead of Site 1. Please correct me if I am wrong.

My next question is how can I had a second =Trim like above? My assumption would like this below. Again correct me if I am wrong.

=IF(AND(C$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")=TRIM(SUBSTITUTE($C13,"MY TEXT","")))*($E$3:$E$10=C$13)),"")

In my master tracker I have another substitution I need input in column E instead of D and the example above is how I was thinking it would be implemented however, I am receiving #VALUE! in the cell instead of the number. The "MY TEXT" is what I am trying to substitute so the formula will only count FRP instead of "MY TEXT FRP". I hope this was descriptive enough. Please let me know if there any questions.

I adjusted my example spreadsheet from the previous posts so maybe everyone can see what I am trying to do. You will see my issue in Column F. I think if this can be resolved I should be set. Thanks for all the help.

Vehicle Inventory 3.xlsx

Good Day!!! Thanks for all the help. I tried the formula out and it doesn't seem to work with my master tracker. Is there a way for the formula to look for the "Type III" or "ORV II" in column E. The Formula works great when I am looking a single vehicle like in column D. I need the formula to also generate the number for all Models that are specific to each District / Site like we did previous columns. I do apologize if this getting to be more complicated.

I understand, I am somewhat experienced with excel but not as experienced as the people on here. I understand the need to walk away and I appreciate all the help AliGW. I did make some errors on the example sheet and I will be sure for the future it is more accurate to limit the back and forth. Rorya I do not disagree that it would be simpler if things were more consistent. Unfortunately this isn't my design per say with inconsistencies on the vehicles, its how the upper management want it. I will go ahead mark this forum as solved and try and figure another way out. I appreciate everyone's input and help!!!!!

I understand. I will leave this open. The last spreadsheet I posted is the most accurate representation of the master tracker. I would love to just post the master tracker but unfortunately I cannot post it on here due to it being proprietary. Again thanks for all the help and advice on everything.

I want to thank everyone for the help and support. The issue has been resolved. Please checkout the updated spreadsheet and I will post the formula that resolved the issue. Thanks again everyone!!

Formula:

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),"")


Vehicle Inventory 5.xlsx