CODEWITHSUNDEEP
excelexcel-formula
extensionhelp
extensionhelp

Reputation: 574

Get Count of MODE

I have some array formulas that are returning the mode of a given value:

=IFERROR(IF(D5=0,"",IF(D5=1,VLOOKUP(B5,'Sup Response Pnts'!$B:$D,3,FALSE),MODE(IF('Sup Response Pnts'!$B$4:$B$3000=B5,'Sup Response Pnts'!$D$4:$D$3000,"")))),"")

Cell D5 is a record count. So if there's only one record, I return that record's value with VLOOKUP. This formulas works, but Excel's MODE returns only the lowest mode. I'd like to add another column which returns the count of MODEs so I can see how often a record has multiple modes.

I attempted to use MODE.MULT on the 'Sup Response Pnts' tab to get a list of all the MODEs, but it returned only one MODE for each value:

=TRANSPOSE(MODE.MULT(IF(B$4:B$3000=B4,C$4:C$3000,"")))

This formula returned only one MODE for each value, even when I put in some dummy data that contained two modes. This formula, if it worked, still wouldn't be ideal because I'd rather not take up a bunch of columns with the result. A single result - the COUNT of MODEs - would be best.

Thanks!

Upvotes: 2

Views: 293

Answers (1)

Michal
Michal

Reputation: 5998

This should work for you Ctrl+Alt+Enter =COUNT(MODE.MULT(RANGE))

Upvotes: 3

Related Questions