Max IF minus criteria in date range

Please help.............

I current use a formula {=MAX(IF($E4:$Z4="F",$E$3:$Z$3))} that looks at a range of cell values for "F" and then returns the max date that corresponds to "F".

This date ref is then used in =SUM(TODAY()-***this returned date***)

But I need the =SUM formula to deduct the amount of "S" cell values since the MAX "F" date


Difficult to explain so I have attached a example spreadsheet with a more detailed query

Try this in B4:

=TODAY()-MAX(C4:D4)-COUNTIFS(INDEX(E4:Z4,MATCH(MAX(C4,D4),E3:Z3,0)):Z4,"S")

It subtracts the number of "S" values in the range from where the max of C4 and D4 occur up to the end of the range.

Hope this helps.

Pete

That's perfect, thank you very much

Glad it worked for you, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete