Help with possible Nested IF?

**vertigo44** · 03-30-2011, 04:25 PM

Link to Document:

http://filebox.vt.edu/users/kkhan/Excel/ExcelHelp2.xls
My current Formula:

=IF(OR(AND(EXACT(A6,A7),EXACT(B6,B7),EXACT(D6,D7),EXACT(E6,E7),EXACT(F6,F7)),AND(EXACT(A7,A8),EXACT(B7,B8),EXACT(D7,D8),EXACT(E7,E8),EXACT(F7,F8))),G8,0)
What i want to be able to do is say when the
#6 rows = #7 rows then use G6
But when
#7 rows = the #8 rows then use G8

As I have it now I can only specify one TRUE which is to use cell G8.

Thanks!

**NBVC** · 03-31-2011, 07:44 AM

Do you mean?

=IF(G6="",IF(SUMPRODUCT(--(EXACT(A6:F6,A7:F7)))=6,G7,""),"")

copied down

**vertigo44** · 03-31-2011, 08:53 AM

Originally Posted by NBVC

Do you mean?

=IF(G6="",IF(SUMPRODUCT(--(EXACT(A6:F6,A7:F7)))=6,G7,""),"")

copied down

Sorry I should have been more descriptive on what needs to happen. I have updated the link to a new excel sheet that shows what I need to happen in this forumla. I'm almost there its just a matter of conditions such as an OR and another IF I believe.

Thanks for the attempt though I do appreciate it!

**NBVC** · 03-31-2011, 12:09 PM

Does this work then?

=IF(G2<>"",G2,IF(SUMPRODUCT(--(A1:F1=A2:F2))=COLUMNS(A2:F2),G1,G3))

I am not sure you need EXACT... exact is case sensitive.

**vertigo44** · 03-31-2011, 01:55 PM

THAT'S IT!!! AMAZING!

Thank you!!!

**vertigo44** · 03-31-2011, 02:51 PM

Originally Posted by NBVC

Does this work then?

=IF(G2<>"",G2,IF(SUMPRODUCT(--(A1:F1=A2:F2))=COLUMNS(A2:F2),G1,G3))

I am not sure you need EXACT... exact is case sensitive.

What does the -- do after sumproduct?

**Colin Legg** · 03-31-2011, 02:54 PM

Hi,

What does the -- do after sumproduct?

SUMPRODUCT() works with numbers, so the -- is used to coerce the boolean TRUE and FALSE values to 1s and 0s.

See here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html