Counting most common text value of column - within date range

[Fishboy]

Hi all,

I am currently using the following formula in Excel 2010:

=INDEX('Contact History'!C:C,(MODE(MATCH(C:C,C:C,0))))

What it does is look at the sheet called "Contact History", checks column C and reports the most common contact name. So far so good.

What I am trying to add in to the mix is for it to also check "Contact History" column A for a date range (say 01/01/15 to 31/01/15 for example) first, then report the most common contact name within that date range.

Is there a way to add the following?:

'Contact History'!$A:$A,">="&DATE(2015,1,1),'Contact History'!$A:$A,"<="&DATE(2015,1,31))

 

[Domenic]

Try...

=INDEX('Contact History'!C:C,MODE(IF('Contact History'!$A:$A>=DATE(2015,1,1),IF('Contact History'!$A:$A<=DATE(2015,1,31),MATCH(C:C,C:C,0)))))

To ignore blanks that may exist in Column C, try...

=INDEX('Contact History'!C:C,MODE(IF('Contact History'!$A:$A>=DATE(2015,1,1),IF('Contact History'!$A:$A<=DATE(2015,1,31),IF('Contact History'!C:C<>"",MATCH(C:C,C:C,0))))))

Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER. Although, I'd suggest that you avoid using whole column references, which can be resource intensive. If the range increases over time, convert your data into a table or use dynamic named ranges. In either case, the range automatically adjusts as data is added or removed.

Hope this helps!

 

[Fishboy]

Hi Domenic,

Both of these work perfectly! Many thanks mate!

 

[Domenic]

You're very welcome!