Formula request

[linga]

Dear all,

Formula required:
if material has same price count should be 1 , if it has multiple price that count should be shown in result column.

[TABLE="width: 144"]
<colgroup><col style="width: 48pt;" span="3" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: #E6B8B7"]Material[/TD]
[TD="class: xl63, width: 64, bgcolor: #E6B8B7"]Price[/TD]
[TD="class: xl63, width: 64, bgcolor: #E6B8B7"]Result[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]X[/TD]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]X[/TD]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]X[/TD]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Y[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Y[/TD]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Y[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[/TR]
</tbody>[/TABLE]

Regards,
Linga

 

[Gerald Higgins]

Just so that we're clear, is the following scenario possible, and if yes, what do you want to do about it ?

Material..Price Result
Z...........1.......?
Z...........1.......?
Z...........2.......?

 

[Marcelo Branco]

Maybe...

Array formula in C2 copied down
=SUM(IF(FREQUENCY(IF($A$2:$A$7=A2,$B$2:$B$7),B$2:B$7),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[linga]

Sir,

It should be 2

 

[linga]

Sir,

It worked fine as per request thank you very much

Regards,
Linga

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.