Minimum amount to be shown from multiple data sheets

[shaggy72]

Hi , help.

I looking for help with the following problem. Attached is copy of a document we use to record output in production area.
Question.
Howe do I get the following to show the minimum amount?

=SUMIF('Line data'!C5:C133,'Line Data Weekly'!A6,'Line data'!I5:I133) this shows the total amount.
But I am looking to show the minimum figure excluding blanks and zero's.

Help please

 

[Sam Mathai Chacko]

Enter as array formula

=SUMPRODUCT(MIN(IF('Line data'!I5:I133*('Line data'!C5:C133='Line Data Weekly'!A6),'Line data'!I5:I133)))

 

[shaggy72]

Awesome - thank you for this.

With this formula combined with " Control + Shift + Enter" this works prefect.

Star

 

[Debraj]

Hi Sam,
Lil bit curious.. Do you have any specific reason for outer SumProduct..

{=MIN(IF('Line data'!I5:I133*('Line data'!C5:C133='Line Data Weekly'!A6),'Line data'!I5:I133))}

 

[Xiq]

Or a pivot table! :D

 

[Sajan]

Hi,
I am sure Sam will agree that the following simplification would also work:
=MIN(IF('Line data'!C5:C133='Line Data Weekly'!A6,'Line data'!I5:I133))

entered with Ctrl + Shift + Enter

Cheers
Sajan.

 

[Xiq]

Just a note:
I think the arrays in the formula's should get a fixed row reference. So: C5:C133 --> C$5:C$133 etc.

 

[Debraj]

I am sure Sam will agree that the following simplification would also work:

I doubt.. Sam/Shaggy will agree.. as shaggy has used Iferror(...,0) @ 'Line data'!I5:I133 and displayed 0 as blank..

 

[Sam Mathai Chacko]

Deb, no reason for SUMPRODUCT. You are right, it wasn't required.

Sajan, Shaggy mentioned that he wanted to exclude 0s and Blanks. So I thought that was a quick way to cover for that.

 

[Sajan]

Thanks for that clarification Sam and Debraj about 0s and blanks in the range to calculate.

-Sajan.