Nested IF function Help

[Fishstats]

Hi all,

I am trying to use a nested IF function but it won’t work (most likely user error) I was hoping for some direction in getting the function to work the way I want it. I am using Excel 2007.

I have two sheets of data

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[/TR]
[TR]
[TD]Site
[/TD]
[TD]des
[/TD]
[TD]rep
[/TD]
[TD]LOTU
[/TD]
[TD]count
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD]x
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD]y
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]out
[/TD]
[TD]1
[/TD]
[TD]y
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]out
[/TD]
[TD]1
[/TD]
[TD]z
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]124
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD]w
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[/TR]
[TR]
[TD]Site
[/TD]
[TD]Des
[/TD]
[TD]Rep
[/TD]
[TD]LOTU
[/TD]
[TD]w
[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]out
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ideally I would like the counts to show up in the proper LOTU column on the second sheet by entering the equation once and being able to drag and drop. I tried this equation =IF(and(sheet1a=sheet2a2,sheet1b=sheet2b2,sheet1c=sheet2c2, sheet1d=sheet2e1), sheet1e2)

The equation had all the proper freezes in place to allow for dragging and dropping but I only ever got blank cells. When I would break out each step or a couple of steps they always worked. I'm not sure what I am doing wrong but whatever help I can get would be very much appreciated.

Thanks in advance!!!

 

[texasalynn]

is this what you are wanting?

Excel 2003
	A	B	C	D	E	F	G	H
1	Site	Des	Rep	LOTU	w	x	y	z
2	234	in	1		0	1	1	0
3	123	out	1		0	0	1	1
4	234	in	1		0	1	1	0
Sheet2

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=E$1))
F2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=F$1))
G2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=G$1))
H2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=H$1))

 

[TheCman81]

Can you clarify a bit more? It's not really clear! Also can you post your expected results in Sheet2? thanks

 

[Fishstats]

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]

[/TR]
</tbody>[/TABLE]
The results I'd like to see in sheet two are

[TABLE="width: 500"]
<tbody>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[/TR]
[TR]
[TD]Site
[/TD]
[TD]Des
[/TD]
[TD]Rep
[/TD]
[TD]LOTU
[/TD]
[TD]w
[/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD]z
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]out
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]124
[/TD]
[TD]in
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Essentially I'd like the to write the equation once in E2 and then be able to drag across the columns (a total of 1190) and down the rows ( a total of 1500).

Hopefully that clarifies things.

Again Thanks!

 

[texasalynn]

try this

Excel 2003
	A	B	C	D	E	F	G	H
1	Site	Des	Rep	LOTU	w	x	y	z
2	234	in	1			12	5
3	123	out	1				4	6
4	124	in	1		5
Sheet2

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=E$1),(Sheet1!$E$2:$E$6))
F2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=F$1),(Sheet1!$E$2:$E$6))
G2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=G$1),(Sheet1!$E$2:$E$6))
H2	=SUMPRODUCT(--(Sheet1!$A$2:$A$6=$A2),--(Sheet1!$B$2:$B$6=$B2),--(Sheet1!$C$2:$C$6=$C2),--(Sheet1!$D$2:$D$6=H$1),(Sheet1!$E$2:$E$6))

 

[Robert Mika]

Excel 07:
=SUMIFS(Sheet1!$E$2:$E$8,Sheet1!$A$2:$A$8,$A2,Sheet1!$B$2:$B$8,$B2,Sheet1!$C$2:$C$8,$C2,Sheet1!$D$2:$D$8,E$1)

or if there is only one line:

=IF(ISNA(MATCH(TRUE,INDEX(Sheet1!$A$3:$A$7&Sheet1!$B$3:$B$7&Sheet1!$C$3:$C$7&Sheet1!$D$3:$D$7=$A2&$B2&$C2&E$1,),0)),"",INDEX(Sheet1!$E$3:$E$7,MATCH(TRUE,INDEX(Sheet1!$A$3:$A$7&Sheet1!$B$3:$B$7&Sheet1!$C$3:$C$7&Sheet1!$D$3:$D$7=$A2&$B2&$C2&E$1,),0)))