Count if help required for multiple values

[Krishnab4u]

Dear All,

say i have a model master like below

Model Code	Model Name
1234	mustang
4321	fortuner

<tbody>
</tbody>

i have a table like below, where i have to capture the quantity of stock of each model name from Database

Model Name	Stock Quantity
Mustang	=Countif(??) (status as Stock only)
Fortuner	=Countif(??) (status as Stock only)

<tbody>
</tbody>

however, my database will have only Model Code like below

model code	color	status
1234	white	Stock
1234	blue	Invoiced
4321	red	Stock

<tbody>
</tbody>

One simpler way is i can do vlookup with my Model Master to add Model Name as last column in Database.
I dont want to do that for some reason.
Kindly help.


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Model Name
1234	mustang
4321	fortuner

<tbody>
</tbody>

</body>

 

[Aladin Akyurek]

Advise: Instead of putting up suggestions like

=Countif(??) (status as Stock only), try to post the desired values for the data sample.​

 

[Krishnab4u]

Hi,
noted.
The desired value is as follows.

Model Name	Stock Quantity
Mustang	= 1
Fortuner	= 1

<tbody>
</tbody>

 

[Aladin Akyurek]

Isn't the count for Mustang 2?

 

[Krishnab4u]

NO, because one is Invoiced as shown in Status column.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1	Model Code	Model Name	model code	color	status	Model Name	Stock Quantity
2	1234	mustang	1234	white	Stock	Mustang	1
3	4321	fortuner	1234	blue	Invoiced	Fortuner	1
4			4321	red	Stock
5
Sheet1

G2, copied down:

=COUNTIFS($E$2:$E$4,"stock",$C$2:$C$4,VLOOKUP(F2,CHOOSE({1,2},$B$2:$B$3,$A$2:$A$3),2,0))