Find the Min value across Multiple sheet ignore zero

[Excelski]

Hello,

How do i find the minimum value in C25 across multiple sheets, ignoring zero's

Thanks

Kevin

[MarvinP]

Hi Excelski,

If you would update your Profile and tell us what version of Excel you have, it might give a different answer. In Excel 2016 there is a MinIfs() function that is not in older versions of Excel:
https://support.office.com/en-us/art...c-72eef32e6599

After finding which version of Excel you have, I'd then look at "3D formulas" at:
https://support.office.com/en-us/art...2-787d0bc888b6

[leelnich]

Are your worksheets listed somewhere?

[Excelski]

Hi,

Profile now updated, I did try Minifs as a guess but it went present, currently i am working at home on my Mac?
Worksheets are listed.

Thanks for looking

Kevin

[leelnich]

WHERE is the list? (It will be referenced in the formula.)

[Excelski]

Job Number List!J5:J1049

[leelnich]

See Post#9
Try this:
=MIN(IF(N(INDIRECT(Job Number List!J5:J1049 &"!C25"))<>0,N(INDIRECT(Job Number List!J5:J1049 &"!C25"))))

NOTE: This doesn't work if there are blanks or invalid names in the worksheet list.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[leelnich]

See Post#9
Sorry, forgot to mention this is an ARRAY FORMULA*:
=MIN(IF(N(INDIRECT(Job Number List!J5:J1049 &"!C25"))<>0,N(INDIRECT(Job Number List!J5:J1049 &"!C25"))))

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

[leelnich]

OK, found a workaround ARRAY FORMULA* that allows for blank rows in the sheet list:
=1/MAX(IFERROR(1/N(INDIRECT(Job Number List!J5:J1049 &"!C25")),0))

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Excelski]

Hi Lee,

Just figured that out, many thanks works great!
What is the N doing?

Thanks
Kevin

[Excelski]

Getting #DIV/0! error on the workaround

[leelnich]

Getting #DIV/0! error on the workaround

THAT'S what the N() is for. Coerces INDIRECT into returning numbers instead of VALUE# errors. To see the difference, EVALUATE both versions.
(Select the cell, then click Evaluate Formula on the Formulas ribbon and cycle through the calculation.)

ps You'll also get #DIV/0! if you forget to press CTRL+SHIFT+ENTER after editing the formula.

[Excelski]

Forgot CTRL+SHIFT+ENTER!

Thanks again

Kevin

[leelnich]

Happy to help, and thanks for the rep!