Looking for Counting formula

[zahidrock]

I have two sheet in one excel file, column A, B, C available on sheet 1 and F,G,H,I,J,K available on sheet two, i want to get count on sheet 2 G to K column, but on sheet1 A column have multiple name of one, so that i need to use it for Vlookup formula, but with Vlookup formula not give me counting on sheet2.

i also need one thing that when i put OUT on Sheet1 status column on that time i need to automatically remove that product on sheet2.

 

[Aladin Akyurek]

Try to post the sample in a form that can readly be copied into Excel cells using one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste.

 

[zahidrock]

Try to post the sample in a form that can readly be copied into Excel cells using one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste.

Name	Product Code	Price			Name	313-CW	313-B	1282-B	385-CW	1282-W
Network	313-CW	10000			Network
Network	313-CW	10000			Payel
Payel	313-B	10000			Habib
Habib	313-CW	10000			Apple
Apple	1282-B	10000			Maa
Maa	1282-B	10000			Liton
Habib	313-CW	10000			Jonone
Habib	385-CW	10000			LR
Liton	385-CW	10000
Liton	1282-B	10000
Jonone	1282-W	10000
LR	1282-W	10000
LR	313-B	10000

<colgroup><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Thanks. What is the expected outcome for Payel by 313-CW?

 

[zahidrock]

Thanks. What is the expected outcome for Payel by 313-CW?

not only 313-CW i want to get every of this and of course quantity

313-CW

313-B

1282-B

385-CW

1282-W

<tbody>
</tbody>

if you can please read details again, i hope on there you can understand that what i want .

 

[Aladin Akyurek]

not only 313-CW i want to get every of this

313-CW

313-B

1282-B

385-CW

1282-W

<TBODY>
</TBODY>

if you can please read details again, i hope on there you can understand that what i want .

Try to answer the question please. Is it a count? If it's, what is the outcome for Payel/313-CW? Or is it a sum? If it's, what is the outcome?

 

[zahidrock]

Try to answer the question please. Is it a count? If it's, what is the outcome for Payel/313-CW? Or is it a sum? If it's, what is the outcome?

yes i need count like this..

Name	313-CW	313-B	1282-B	385-CW	1282-W
Network	2
Payel		1
Habib	2			1
Apple			1
Maa			1
Liton			1	1
Jonone					1
LR		1			1

<colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody>
</tbody>

One more if sheet1 status column show "OUT" then it need to decrease

Name	Product Code	Price	Status
Network	313-CW	10000	out
Network	313-CW	10000
Payel	313-B	10000
Habib	313-CW	10000
Apple	1282-B	10000	out
Maa	1282-B	10000
Habib	313-CW	10000
Habib	385-CW	10000	out
Liton	385-CW	10000
Liton	1282-B	10000	out
Jonone	1282-W	10000
LR	1282-W	10000	out
LR	313-B	10000

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

after out added on status column result should be.

Name	313-CW	313-B	1282-B	385-CW	1282-W
Network	1
Payel		1
Habib	2			0
Apple			0
Maa			1
Liton			0	1
Jonone					1
LR		1			0

<colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Let A:D house the data, F:K the processing.

G1, copy across and down:

=COUNTIFS($A:$A,$F2,$B:$B,G$1,$D:$D,"<>OUT")

 

[zahidrock]

Let A:D house the data, F:K the processing.

G1, copy across and down:

=COUNTIFS($A:$A,$F2,$B:$B,G$1,$D:$D,"<>OUT")

Its working on same sheet, but i cant make it on two different sheet,

My data on Sheet1

Name	Product Code	Price	Status
Network	313-CW	10000	Out
Network	313-CW	10000	Out
Payel	313-B	10000
Habib	313-CW	10000
Apple	1282-B	10000
Maa	1282-B	10000	Out
Habib	313-CW	10000
Habib	385-CW	10000
Liton	385-CW	10000	Out
Liton	1282-B	10000	Out
Jonone	1282-W	10000	Out
LR	1282-W	10000
LR	313-B	10000

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

but i want result on Sheet2

Name	313-CW	313-B	1282-B	385-CW	1282-W
Network	0	0	0	0	0
Payel	0	1	0	0	0
Habib	2	0	0	1	0
Apple	0	0	1	0	0
Maa	0	0	0	0	0
Liton	0	0	0	0	0
Jonone	0	0	0	0	0
LR	0	1	0	0	1

<colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Its working on same sheet, but i cant make it on two different sheet,

My data on Sheet1

Name	Product Code	Price	Status
Network	313-CW	10000	Out
Network	313-CW	10000	Out
Payel	313-B	10000
Habib	313-CW	10000
Apple	1282-B	10000
Maa	1282-B	10000	Out
Habib	313-CW	10000
Habib	385-CW	10000
Liton	385-CW	10000	Out
Liton	1282-B	10000	Out
Jonone	1282-W	10000	Out
LR	1282-W	10000
LR	313-B	10000

<TBODY>
</TBODY>

but i want result on Sheet2

Name	313-CW	313-B	1282-B	385-CW	1282-W
Network
Payel		1
Habib	2			1
Apple			1
Maa
Liton
Jonone
LR		1			1

<TBODY>
</TBODY>

Sheet1, A:D, houses the data.

Sheet2, A:F, houses the set up for counting...

Sheet2, B2, copied across and down:

=COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1,Sheet1!$D:$D,"<>OUT")