# counting types of material

#### sambit

##### Member
Dear Sir,
plz resolve the issue. Example file attached for your reference.

#### Attachments

• 9.4 KB Views: 7

#### vletm

##### Excel Ninja
sambit
Was Your expected results as someway solved in OutPut-column?
What are types of material?
... Where has You used that term?
... or something like this?

#### Attachments

• 13.5 KB Views: 4

#### sambit

##### Member
vletm sir,
types of material mean types of material in material coloum based on invoice no.

 Invoice No Material Output Output Ans should be like below 2190216929 MS ROD 1 2190216929 MS ROD 1 2190216929 CEMENT 2 2190216932 MS ROD 1 2190216932 MARBEL 2 2190216934 MS ROD 1 2190216934 MS ROD 1 2190216934 SS ROD 2 2190216934 MARBEL 3 2190216934 MARBEL 3

#### GraH - Guido

##### Well-Known Member
sambit,
Care to explain why "Cement" at row 3, should be counted as 2.
What is the applied "count" logic?

#### bosco_yip

##### Excel Ninja
types of material mean types of material in material column based on invoice no.
 Invoice No Material Output Output Ans should be like below 2190216929 MS ROD 1 2190216929 MS ROD 1 2190216929 CEMENT 2 2190216932 MS ROD 1 2190216932 MARBEL 2 2190216934 MS ROD 1 2190216934 MS ROD 1 2190216934 SS ROD 2 2190216934 MARBEL 3 2190216934 MARBEL 3
Try,

In C2, formula copied down :

=SUMPRODUCT(((A\$2:A2=A2))/COUNTIFS(A\$2:A2,A\$2:A2&"",B\$2:B2,B\$2:B2&""))

Last edited:

#### vletm

##### Excel Ninja
sambit
types of material mean types of material in material coloum based on invoice no.
Did You explain how output Ans should be like Your given values?
As GraH - Guido asked What is the applied "count" logic?
So far my logic give this ...

#### vletm

##### Excel Ninja
sambit
Seems You cannot answer ... What is the applied "count" logic?
Ps. All formulas working fine - some of those formulas will give even correct answers.

#### GraH - Guido

##### Well-Known Member
Not the first time, and certainly not the last time, @bosco_yip is able to provide a correct answer when nobody else seems to understand the slightest bit of the OP's request. How do you do that?

@vletm, following Bosco's it is about counting the number of different materials inside a single invoice number. But I'm stating the obvious for you, aren't I?

This could work as well? [E4]=((C4=C3)*N(F3))+OR(C4<>C3,D4<>D3)

#### Attachments

• 10.5 KB Views: 6

#### vletm

##### Excel Ninja
GraH - Guido
If OP cannot explain 'Opself' what do want? how to get expected results?
... then even OP don't and cannot know what are correct results.
As You asked ... there seems to be one CEMENT ... and ... count of something is more than one ...
Do that mean, others should try to get same values as those expected (no matter, what have asked)?
As I wrote: All formulas working fine - some of those formulas will give even correct answers.

#### bosco_yip

##### Excel Ninja
Not the first time, and certainly not the last time, @bosco_yip is able to provide a correct answer when nobody else seems to understand the slightest bit of the OP's request. How do you do that?
This could work as well? [E4]=((C4=C3)*N(F3))+OR(C4<>C3,D4<>D3)
It is a "Count unique values based on criteria in another column" question

And,

Your shorter formula, after making some trick that can remove N() and become :

=(C4=C3)*E3+OR(C4<>C3,D4<>D3)

How to do that ?

Last edited:

Yes !