Sumproduct question

[TheNoocH]

Book1
	A	B	C	D	E
1	Dept	Date	Shift	Data
2	Dept1	11/11/2005	1	0
3	Dept1	11/11/2005	2	0
4	Dept1	11/12/2005	1	98
5	Dept1	11/12/2005	2	0
6	Dept1	11/13/2005	1	98
7	Dept1	11/13/2005	2	98
8
9
10	Dept	Shift	Data		Adj Formula
11	Dept1	1	196		98
12	Dept1	2	98		98
Sheet1

I have data similar to the example i posted...i was using the first date in the range (b/c I had a field which showed the date) to pull in the data...all i want to pull in is the first time the data isnt' 0....obviously just doing a straight sumproduct (my data column) on the data adds them all up ....in my adjusted formula column is the best i could come up with knowing that the data will all be the same excluding the 0's....is this the best way (obviously i'm thinking it's not)....can anyone offer up any suggestions...thanks

 

[Domenic]

Try...

C11, copied down:

=INDEX($D$2:$D$7,MATCH(1,($A$2:$A$7=A11)*($C$2:$C$7=B11)*($D$2:$D$7<>0),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[barry houdini]

I'd say that SUMPRODUCT is not usually the best function when you're doing some sort of lookup, although it can often be manipulated to work, as you know.

How about this array formula

=INDEX($D$2:$D$7,MATCH(1,($A$2:$A$7=$A11)*($C$2:$C$7=$B11)*($D$2:$D$7<>0),0))

confirmed with CTRL+SHIFT+ENTER

edit: too late

 

[TheNoocH]

Domenic and Barry,
Thanks...i gotta run so i won't be able to test it till tomorrow...but i'm assuming since you have the same exact formula that it should work...
i appreciate the response...

 

[vane0326]

This is a non-array formula.


=LOOKUP(2,1/((A2:A7=A11)*(C2:C7=B11)),D2:D7)

 

[Domenic]

This is a non-array formula.


=LOOKUP(2,1/((A2:A7=A11)*(C2:C7=B11)),D2:D7)

Since the OP is looking for a non-zero value, if the data is the same (except for zeros)...

=LOOKUP(2,1/(($A$2:$A$7=A11)*($C$2:$C$7=B11)*($D$2:$D$7<>0)),$D$2:$D$7)

However, I'm not sure the data will always be the same...

 

[TheNoocH]

Guys, thanks for your suggestions...gonna be tied up again today...but i think i understand what you are doing and i should be able to apply to my situation...

In THEORY the data SHOULD be the same...so i'll evaluate both formulas and see what works best for my situation....thanks again...