# 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 Item01-Jan-17 Apple01-Jan-17 Apple01-Jan-17 Orange01-Feb-17 Apple02-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!

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

#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)

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-173 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)?

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.

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!

Report •