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

DerbyDad03 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

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

DerbyDad03 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

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