Sumproduct question

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
Book1
ABCDE
1DeptDateShiftData
2Dept111/11/200510
3Dept111/11/200520
4Dept111/12/2005198
5Dept111/12/200520
6Dept111/13/2005198
7Dept111/13/2005298
8
9
10DeptShiftDataAdj Formula
11Dept1119698
12Dept129898
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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!
 
Upvote 0
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 :biggrin:
 
Upvote 0
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...
 
Upvote 0
vane0326 said:
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... :)
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top