• 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
Dear Sir,
plz resolve the issue. Example file attached for your reference.
 

Attachments

  • EXAMPLE EXCEL ISSUE.xlsx
    9.4 KB · Views: 7
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

  • EXAMPLE EXCEL ISSUE.xlsx
    13.5 KB · Views: 4
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
 
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:
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
 
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.
 
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

  • EXAMPLE EXCEL ISSUE Count inside invoice.xlsx
    10.5 KB · Views: 6
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.
 
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:
Back
Top