Most Popular Entry by Month

**tomlancaster** · 08-11-2009, 08:15 AM

Hello, I have been kindly given the following code to work out the most frequently occurring entry is in a list:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX('All Incidents'!$D$1:$D$1085,MODE(IF(('All Incidents'!$D$1:$D$1085<>"")*ISNA(MATCH('All Incidents'!$D$1:$D$1085,$E$10:$E10,0)),MATCH('All Incidents'!$D$1:$D$1085,'All Incidents'!$D$1:$D$1085,0))))))

Committed with CTRL + SHIFT + ENTER

What I would like to do is adapt it so it shows the most popular entry for a Month

I have a collumn in the spreadsheet with the numeric month value 1 being January through to 12 being December

These numeric month values are in collumn "O"

I have a COUNTIF and SUMPRODUCT statement but I didn't get anywhere.

Can anyone help?

**Cheeky Charlie** · 08-11-2009, 08:23 AM

use a pivot table!

Post your data, I'll talk you through how

**DonkeyOte** · 08-11-2009, 08:26 AM

If a PT is not viable given your tabular requirements and/or result being non-numeric value (ie not viable in PT data field) you can adjust the previous formula as so:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX('All Incidents'!$D$1:$D$1085,MODE(IF(('All Incidents'!$D$1:$D$1085<>"")*('All Incidents'!$O$1:$O$1085=1)*ISNA(MATCH('All Incidents'!$D$1:$D$1085,$E$10:$E10,0)),MATCH('All Incidents'!$D$1:$D$1085,'All Incidents'!$D$1:$D$1085,0))))))

Committed with CTRL + SHIFT + ENTER

The alteration is in red - and obviously reference to 1 can be altered to be a cell containing value of interest.