Mode Function combined with a filter

  • Thread starter Thread starter Neolyth
  • Start date Start date
N

Neolyth

Hi
I'd like to use the mode-function like sumifs or countifs. So first I want
to do a filtering and then use the resulting list to calculate the modal
value. How can I do this?

Thanks in advance
 
Say we have data in column A, from A1 thru A19:

data
1
1
1
1
2
2
2
3
3
4
5
6
7
8
9
10
11
12

Where A1 is the header cell. Use:

=MODE(IF(SUBTOTAL(3,OFFSET(A2:A19,ROW(A2:A19)-ROW(A2),,1))=1,A2:A19,""))

This is an array function that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
 
Back
Top