formula to find max number of values

Ian Bell
Ian Bell used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
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
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Ian Bellretired

Author

Commented:
Thanks Shums, you're the best.
The first solution worked perfectly
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Ian! Glad I was able to help.
BTW, change the formula to automatically update in Excel Options.
Ian Bellretired

Author

Commented:
Thanks Shuims, however for this sheet I prefer a manual calc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial