# Thread: MAX IF to ignore NA with multiple conditions Thanks:  1 Post #5259013 (1) Likes: 0

1. ## Re: MAX IF to ignore NA with multiple conditions

When I apply the array, the row I'm trying to find the maximum in (row 24 of another sheet) returns #N/A.

Simply put, I have a sheet called 'data' that has formulas in it. In row 24 of that sheet, I have formula =IF(E31>0,(number*number*E31),NA()) so that if there is no data in cell E31, it will return #N/A. I did it so that it automatically plots a chart on another sheet.

There is another sheet in the same file called 'trends' where I have two tables that show the average and max. In order for these tables to populate, they must meet two conditions.

{=MAX(IF('data'!\$3:\$3=\$A\$1,IF('data'!\$4:\$4=\$A15,'data'!\$24:\$24)))} - so if cell A1 of the 'trends' sheet shows up in row 3 of 'data' sheet, and if cell A15 of the 'trends' sheet shows up in row 4 of the 'data' sheet, it will find the maximum in row 24 of 'data' as long as the two conditions are met. If there is #N/A in any of those cells, it' returns #N/A in the table I created.

2. ## Re: MAX IF to ignore NA with multiple conditions

Originally Posted by ellisbobby
When I apply the array, the row I'm trying to find the maximum in (row 24 of another sheet) returns #N/A.
The row does not return #N/A, the formula does.

Simply put, I have a sheet called 'data' that has formulas in it. In row 24 of that sheet, I have formula =IF(E31>0,(number*number*E31),NA()) so that if there is no data in cell E31, it will return #N/A. I did it so that it automatically plots a chart on another sheet.
The formula as I edited

=MAX(IF(data!\$3:\$3=\$A\$1,IF(data!\$4:\$4=\$A16,IF(ISNUMBER(data!\$24:\$24),data!\$24:\$24))))

tests the values for being numbers and those which are numbers are delivered to the outer MAX, so if there are no #N/A's in other rows which are referred to
this formula should succeed.

There is another sheet in the same file called 'trends' where I have two tables that show the average and max. In order for these tables to populate, they must meet two conditions.

{=MAX(IF('data'!\$3:\$3=\$A\$1,IF('data'!\$4:\$4=\$A15,'data'!\$24:\$24)))} - so if cell A1 of the 'trends' sheet shows up in row 3 of 'data' sheet, and if cell A15 of the 'trends' sheet shows up in row 4 of the 'data' sheet, it will find the maximum in row 24 of 'data' as long as the two conditions are met. If there is #N/A in any of those cells, it' returns #N/A in the table I created.
It seems you have #N/A's in other rows too. In that case, let's try:

Control+shift+enter, not just enter...

=MAX(IF(ISNUMBER((data!\$3:\$3=\$A\$1)*(data!\$4:\$4=\$A16)*(data!\$24:\$24)),data!\$24:\$24))

3. ## Re: MAX IF to ignore NA with multiple conditions

Thank you so much! There were three of us trying to get this to work for week. Haha.

I appreciate your help. Thanks again!

4. ## Re: MAX IF to ignore NA with multiple conditions

Originally Posted by ellisbobby

Thank you so much! There were three of us trying to get this to work for week. Haha.

I appreciate your help. Thanks again!
You are welcome.