Example:- Date Item
01-Jan-17 Apple
01-Jan-17 Apple
01-Jan-17 Orange
01-Feb-17 Apple
02-Feb-17 AppleTo find out how many type of item in the datasheet (each item only count once)
=SUMPRODUCT((Item list<>"")/countif(item list, item list&""))But, how to find out how many type of item in a month?
For example:-
Jan-17 - Apple (x2) and Orange (x1)
- Type of item in Jan-17 should be 2 (apple & orange)
- Total qty of item in Jan-17 equals to 3How can I get the result of 2 (type of item) by using formula?
Please help on this. Thanks!
First, a posting tip:Please click on the How To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Thanks!
If you are starting with this...
A B 1 Date Item 2 01-Jan-17 Apple 3 01-Jan-17 Apple 4 01-Jan-17 Orange 5 01-Feb-17 Apple 6 02-Feb-17 Apple...you could use this:
=COUNTIFS(A2:A6,">="&A2,A2:A6,"<"&EDATE(A2,1),B2:B6,B2)
or, you could use a Helper Column containing the MONTH function...
A B C 1 Date Month Item 2 01-Jan-17 1 Apple 3 01-Jan-17 1 Apple 4 01-Jan-17 1 Orange 5 01-Feb-17 2 Apple 6 02-Feb-17 2 Apple...and then use this:
=COUNTIFS(B2:B6,B2,C2:C6,C2)
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code
Thanks for your prompt reply. I've tried your formula. Unfortunately, it's not the result which I want to get.
I might not clear in my question in earlier posting.
Therefore, I would re-address my question as below:-From data in earlier posting,
01-Jan-17
3 inputs (apple, apple, orange)
2 types of fruit (apple, orange)Is there any formula that can find out "no. of type of fruit" in 01-Jan-17 (each fruit only count once)?
A further question, just for clarity: Are you looking for the number of unique items in a given month or for a given day?
All of your January dates are 01, but your February dates are 01 & 02.That adds a little confusion to question.
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code
Sorry for confusing. Yes. I'm looking for the number of unique items in a given MONTH.
Thanks!