• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

counting types of material

sambit

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


Invoice NoMaterialOutputOutput Ans should be like below
2190216929MS ROD1
2190216929MS ROD1
2190216929CEMENT2
2190216932MS ROD1
2190216932MARBEL2
2190216934MS ROD1
2190216934MS ROD1
2190216934SS ROD2
2190216934MARBEL3
2190216934MARBEL3
 

bosco_yip

Excel Ninja
types of material mean types of material in material column based on invoice no.
Invoice NoMaterialOutputOutput Ans should be like below
2190216929MS ROD1
2190216929MS ROD1
2190216929CEMENT2
2190216932MS ROD1
2190216932MARBEL2
2190216934MS ROD1
2190216934MS ROD1
2190216934SS ROD2
2190216934MARBEL3
2190216934MARBEL3
Try,

In C2, formula copied down :

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

72807
 
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 ...
Screenshot 2021-01-04 at 15.55.21.png
 

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

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)

72834

How to do that ?
 
Last edited:
Top