Frequent Words in a filtered list

[Ovaly]

Hello all, hope you guys can help. I have been all over the net searching for a resolution with no success.

I have 3 coloums of data. A - Month, B - Employee, C - Model
My problem is I would like a formula that will give me the most frequent model, ignoring hidden cells.

I am using this current formula:
=INDEX($G$14:$G$985,MODE(IF(($G$14:$G$985<>"")*ISNA(MATCH($G$14:$G$985,$G$14:$G$14,0)),MATCH($G$14:$G$985,$G$14:$G$985,0))))

But yeah, once i filter the spreadsheet it kinda becomes redundant.
I have tried UDF with visible cells only and rowheight formulas to no avail.
I have a feeling its something simple that i am missing, any help would be appreciated.

Cheers

Johnny G

 

[Michael M]

Hi
Have you considered using a Pivot table
That would give you a count of ALL models

 

[Ovaly]

I have looked at pivot tables, but its not quite what i am after. My spreadsheet contains quite a bit of info but what i am trying to do is just give a snapshot of data so i can find trends. A formula in this case would work a lot better.

Cheers for your reply though.

Johnny G

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX($G$14:$G$985,MODE(IF(SUBTOTAL(3,OFFSET($G$14,
    ROW($G$14:$G$985)-ROW($G$14),,1)),IF($G$14:$G$985<>"",
     MATCH($G$14:$G$985,$G$14:$G$985,0)))))

Note. Model entries mustn't have any wild chars round them like < and >, etc.

 

[Ovaly]

Thankyou so much Aladin, works perfectly. Just out of curiosity though, is there anyway to make the array auto calculate after a filter has been changed?
Secondly i was using the following formula to work out the second most frequent word.
=INDEX($G14:$G985,MODE(IF(($G14:$G985<>"")*ISNA(MATCH($G14:$G985,B10,0)),MATCH($G14:$G985,$G14:$G985,0))))

What would i need to do to adjust the formula you gave me for this result?

Thankyou again, i was starting to pull my hair out.

Johnny G

 

[Ovaly]

Ok i figured out the auto calculate problem. But if you could help to adjust the formula you gave me previously to give results for 2nd & 3rd most frequent i would be forever in your debt. (The Frequency results are in B10, B11(2nd) and B12(3rd))

Cheers

Johnny G

 

[Aladin Akyurek]

Ok i figured out the auto calculate problem. But if you could help to adjust the formula you gave me previously to give results for 2nd & 3rd most frequent i would be forever in your debt. (The Frequency results are in B10, B11(2nd) and B12(3rd))

Cheers

Johnny G

B9: Most Frequent

B10, control+shift+enter, not just enter, and copy down:

=INDEX($G$14:$G$985,MODE(IF(SUBTOTAL(3,OFFSET($G$14,
   ROW($G$14:$G$985)-ROW($G$14),,1)),IF($G$14:$G$985<>"",
    IF(1-ISNUMBER(MATCH($G$14:$G$985,$B$9:B9,0)),
     MATCH($G$14:$G$985,$G$14:$G$985,0))))))

 

[Ovaly]

Perfect... Aladin, You sir are a legend.

Thankyou for your help, I'm sure i'll be back come the next hurdle.

Cheers

Johnny G