How to find out how many type of items occur in each month

August 23, 2017 at 20:01:31
Specs: Windows 7
Example:-

Date Item
01-Jan-17 Apple
01-Jan-17 Apple
01-Jan-17 Orange
01-Feb-17 Apple
02-Feb-17 Apple

To 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 3

How can I get the result of 2 (type of item) by using formula?

Please help on this. Thanks!


See More: How to find out how many type of items occur in each month

Reply ↓  Report •

#1
August 24, 2017 at 19:54:27

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


Reply ↓  Report •

#2
August 24, 2017 at 20:40:43
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)?


Reply ↓  Report •

#3
August 25, 2017 at 07:04:37
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


Reply ↓  Report •

Related Solutions

#4
August 27, 2017 at 17:31:07
Sorry for confusing.

Yes. I'm looking for the number of unique items in a given MONTH.

Thanks!


Reply ↓  Report •

Ask Question