Finding Max value based on miltiple criteria - too many arguments Hello, I have

Question

Finding Max value based on miltiple criteria - too many arguments

Hello,

I have a spread sheet with about 50,000 lines and 7 columns. These are phone records.

Column 7 has the call duration and the other columns contain unique data about each call, month, day, hour, agent, type, etc...

I am looking for the longet call that meets the specific criteria in the other 6 columns.

I use {=MAX(IF($A:$A=1,$B:$B=D1,$G:$G))} and partially get what i need. But when I expand it to include more criteria I get an error about too many arguments.

I've tried {=MAX(($A:$A=1)*($B:$B=E1)*($G:$G))} but only get #VALUE! returned.

How can this formula be adjusted or rewritten to provide the max time value when 6 other criteria are met?

thank you

Explanation / Answer

%Try this test data, paste it into A1 of an empty sheet:)D

Now the formula which based upon the 6 criteria will return 9999@$^

=MAX(IF(A1:A10=1,IF(B1:B10=2,IF(C1:C10="x",IF(D1:D10="y",IF(E1:E10="z",IF(F1:F10=99,G1:G10)))))))

Note this is an ARRAY formula, see below. Also note if the criteria is text then enclose it in quotes. Numeric criteria doesn't have quotes

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.