max if multiple conditions, ignoring blanks

[robotlust]

Hi All,
I'm right on the edge of solving this but cannot understand why one aspect of my sumproduct is being ignored.

Please Login or Register  to view this content.

What I want to do is show the maximum Days Pending per company in a list. The column that shows days pending already has a function in it - giving either a number value or ="".

I've been able to get the function to ignore the blanks in the list, but I'm getting the same value for all 3 Company results. i.e they should be different.
What have I done wrong?

[npamcpp]

A little data sample might help. Mock up the expected results manually and upload a file that can be used for testing.

[icestationzbra]

if i understand you correctly, you may not need SUMPRODUCT, just an Array-MAX. something like this (untested):

{=max((company=a6)*(status=d4)*days_pending)}

and since you are trying to find MAX, you may not even need the "(days_pending<>"")" portion...

[robotlust]

Hi,
I've tried tusing MAX as an array but i get #VALUE errors. perhaps because i have ="" as values in the MAX range.

Attached example. green cells are the output and desired result in column H

Example v0-1.xls

[robotlust]

P.S. +1 day to the desired results. It's counting days...

[ron2k_1]

This can give you your answers, however, it is because your dates in C are entered in ascending order:

Please Login or Register  to view this content.

This occurs because "Pending Age" will always be in descending order. However, in the event where "Pending Age" will not be listed in descending order (meaning that you can find values in lower rows that are higher than values that can be found in the upper rows). Then the formula will not work as it will only give you the first instance where that happens.

Someone else here may give you a nicer array that may be more reliable. I'm really not good with arrays...

[Cutter]

The array formula would be:

=MAX(IF(($D$244:$D$332=A6)*($E$244:$E$332=$D$4),$B$244:$B$332)) confirmed with Ctrl+Shift+Enter

and drag down