Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

Modified Max() formula depending on values in adjacent column in Excel

(Edit note: Apologies for the images being so large but I don't know of a way to reduce their size.)

Hello,

In Excel, how do you modify the =Max() function (or some other function) so that it evaluates only those cells in a given range for which an adjacent cell meets some specified criteria?

For example, suppose you have a spreadsheet in which Column C contains simple numbering from 1 to 60 (as a timeline in seconds) as shown in Fig. 1. And suppose Column D contains production values beginning in the 1st row and continuing down to some other row but then only zeros in the cells below that.

Production continues for 6 seconds in Fig. 1 and 9 seconds in Fig. 2:

User generated imageUser generated image
If the total production is displayed in Cell D2 (Fig. 3), what formula in Cell C4 will display the total number of seconds during which production continued so that the production rate calculated in Cell D6 is accurate (Fig. 4)?

User generated imageUser generated image
In other words, what formula in C4 will display the last second marker in Column C which has an associated Column D value not equal to zero?

Thanks
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Shums

You made it simple. It seems I overthought it. Lol
I assumed there maybe zeros in between the data as well.
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Thank you