Excel/Countif with match and multiple criteria
I am trying to creat a formula for counting the exact match and multiple criteria but couldn't will you please assist me.Below are the detials.
Column B with all the assets (4 Types, Dell 660 Latptop,Dell 1450 desktop,Dell 1560 Laptop,HP 2340 desktop)
Column C with the equipment status. (9 Types,On Loan, On Location, Pending Assembly, RFF, In reline, Need Reline, In M/C Shop and Short Length)
Now I want to calculate
"Avail" (That calculate On Loan + On Location + Pending Assembly + RFF) ,
"Reline" (that calculate Reline)
"To Reline" (that calculate need Reline)
"Machine Shop" (that calculate In M/C Shop)
"Junk" (that calculate Short Length)
I also attached the snap shot as well.
Thanks for your time.
This can be handled using COUNTIFS function in msexcel.
You need to list all the statuses in a separate range, say E2:E10. Assuming your data is in A1 to C25 as shown in your excel, you would need to use the COUNTIFS formula in following combinations:
1. for row 1 (model 660) and column (avail) = =COUNTIFS($B$2:$B$25,A30,$C$2:$C$25,$E$7)+COUNTIFS($B$2:$B$25,A30,$C$2:$C$25,$E$8)+COUNTIFS($B$2:$B$25,A30,$C$2:$C$25,$E$2)+COUNTIFS($B$2:$B$25,A30,$C$2:$C$25,$E$9)
2. for row 1 (model 660) and column (reline) = =COUNTIFS($B$2:$B$25,A30,$C$2:$C$25,$E$3)
Interestingly, once you have the formula working, it will start making sense to you.
Please see the screenshot where I have highlighted the two result boxes.
I think this should meet your requirements.