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

a	b	c	d	e
Site	des	rep	LOTU	count
234	in	1	x	12
234	in	1	y	5
123	out	1	y	4
123	out	1	z	6
124	in	1	w	5

<tbody>
</tbody>

Sheet 2

a	b	c	d	e	f	g	h
Site	Des	Rep	LOTU	w	x	y	z
234	in	1
123	out	1
234	in	1

<tbody>
</tbody>

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]

<tbody>
</tbody>

The results I'd like to see in sheet two are

a	b	c	d	e	f	g	h
Site	Des	Rep	LOTU	w	x	y	z
234	in	1			12	5
123	out	1				4	6
124	in	1		5

<tbody>
</tbody>

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)))