Count if help required for multiple values

[Krishnab4u]

Dear All,

say i have a model master like below
[TABLE="width: 500"]
<tbody>[TR]
[TD]Model Code[/TD]
[TD]Model Name[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]mustang[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]fortuner[/TD]
[/TR]
</tbody>[/TABLE]

i have a table like below, where i have to capture the quantity of stock of each model name from Database
[TABLE="width: 500"]
<tbody>[TR]
[TD]Model Name[/TD]
[TD]Stock Quantity[/TD]
[/TR]
[TR]
[TD]Mustang[/TD]
[TD]=Countif(??) (status as Stock only)[/TD]
[/TR]
[TR]
[TD]Fortuner[/TD]
[TD]=Countif(??) (status as Stock only)[/TD]
[/TR]
</tbody>[/TABLE]

however, my database will have only Model Code like below
[TABLE="width: 500"]
<tbody>[TR]
[TD]model code[/TD]
[TD]color[/TD]
[TD]status[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]white[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]blue[/TD]
[TD]Invoiced[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]red[/TD]
[TD]Stock[/TD]
[/TR]
</tbody>[/TABLE]

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;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Model Name[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]mustang[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]fortuner[/TD]
[/TR]
</tbody>[/TABLE]
</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.
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Model Name[/TD]
[TD]Stock Quantity[/TD]
[/TR]
[TR]
[TD]Mustang[/TD]
[TD]= 1[/TD]
[/TR]
[TR]
[TD]Fortuner[/TD]
[TD]= 1[/TD]
[/TR]
</tbody>[/TABLE]

 

[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))