count unique text by month

[Mike952]

Hi Folks,

I am looking to find how many times a text item occurs per month.

I have attached a spreadsheet to highlight what I have tried so far:
=SUM(--(FREQUENCY(IF($D2:$D50=F$1,MATCH($A2:$A50,$A2:$A50,0)),ROW($A2:$A50)-ROW($A2)+1>0)))

any advice would be much appreciated.

Mike

[Fluff13]

Col D is text but F1 is a date, so try

Please Login or Register  to view this content.

[Mike952]

Thanks for the reply, this certainly produces data, which is a great step forward, but it counts all occurrences not unique entries, so I have made a mistake somewhere else in the formula.

[Czeslaw]

Power Query

Please Login or Register  to view this content.

[Fluff13]

Hadn't noticed one of the brackets was in the wrong place. Try

Please Login or Register  to view this content.

[Mike952]

I have never used the power query, so thank you for highlighting this as an option.

[Mike952]

Hi Folks,

I have realised that the formula works perfectly, but it is not what I need, which is my mistake.

I thought the frequency would pull the unique values only once and the sum would then add up the unique entries.

it is actually summing all occurrences of the text in column A by month.

I need to know the number of times per month each unique value appears (not a sum of occurrences)

so I need a new formula that will achieve this.

is it also possible to have 2nd criteria?

[Fluff13]

Try the 2nd formula that I posted.

[Mike952]

perfect, thank you.

don't know how I missed the second formula.

is it possible to add a second query?

its not essential, but it would be nice to know if the trailers were single or double deck, as double deck use more fuel for each mile travelled due to the greater wind resistance.

I have a formula to pull out all the double deck trailers which I can add to column E, so the additional query in column F would have to look for "Yes" or "No", in column E.

Mike

[Mike952]

Oh,

I cracked it, its simply another IF statement before the Match function.

Thank yo for the help.

[Fluff13]

How about

Please Login or Register  to view this content.