Sumproduct question

TheNoocH

Well-known Member
Book1
ABCDE
1DeptDateShiftData
2Dept111/11/200510
3Dept111/11/200520
4Dept111/12/2005198
5Dept111/12/200520
6Dept111/13/2005198
7Dept111/13/2005298
8
9
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Domenic

MrExcel MVP
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

MrExcel MVP
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.

=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

Well-known Member
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

Well-known Member
This is a non-array formula.

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

Domenic

MrExcel MVP
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...

TheNoocH

Well-known Member
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...

Replies
3
Views
243
Replies
1
Views
129
Replies
0
Views
280
Replies
5
Views
376
Replies
7
Views
153

1,195,909
Messages
6,012,256
Members
441,687
Latest member
urimagic

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.

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

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