formula to find max number of values

Hi,
I would like help to build a formula to find max zero's in a column within a series (ID range)
Please refer to attached sheet
many thanks
Ian
Max_zeros.xlsx
raceproretiredAsked:
Who is Participating?
 
ShumsDistinguished Expert - 2017Commented:
Hi Ian,

Try below:
=COUNTIFS($A$4:$A$25,$A4,$E$4:$E$25,0)

Open in new window


Your cell formatting seems weird, I had to press F2 to refresh those cells. Please find attached...
RacePro_Max_zeros.xlsx
1
 
ShumsDistinguished Expert - 2017Commented:
Another approach would be:
=SUMPRODUCT(($A$4:$A$25=$A4)*($E$4:$E$25=0))

Open in new window


Please remember to press F2 + Enter after you add formula
RacePro_Max_zeros.xlsx
1
 
ShumsDistinguished Expert - 2017Commented:
Also, If you need to find which Series has Maximum Zeros in Col E, you can try below Array Formula confirmed with Ctrl+Shift+Enter:
=INDEX($A$4:$A$25,MODE(IF($E$4:$E$25=0, MATCH($A$4:$A$25,$A$4:$A$25,0))))

Open in new window

RacePro_Max_zeros.xlsx
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
raceproretiredAuthor Commented:
Thanks Shums, you're the best.
The first solution worked perfectly
1
 
ShumsDistinguished Expert - 2017Commented:
You're Welcome Ian! Glad I was able to help.
BTW, change the formula to automatically update in Excel Options.
1
 
raceproretiredAuthor Commented:
Thanks Shuims, however for this sheet I prefer a manual calc
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.