Sumproduct formula help (possible Index????)
in [Functions]
|
Prev: If OR formula
Next: Multi-Condition Formula
From: Mark D on 1 Jun 2010 07:37 Sorry Jacob my mistake. The VALUE was arising as some of the cells in H were blank. I've changed my formula in H accordingly. So that works great thank you. One last additional question if I may. How do I wrap the whole formula to say * A1 I want to take the result against the % in A1 Thanks again "Jacob Skaria" wrote: > Do you mean? > > =SUMPRODUCT((G42:G58<I3)*(B42:B58="EX")*(H42:H58*0.7))+ > SUMPRODUCT((G42:G58<I3)*(B42:B58="Current")*(H42:H58)) > > -- > Jacob (MVP - Excel) > > > "Mark D" wrote: > > > Morning all > > > > I am stuck with a forumula that I hope someone will be able to help with. > > It's quite long winded the way I am doing it but am hoping that it may be > > able to be shortened. > > > > Column B Lines 42 - 58 has either "current" or "ex" in the cells > > > > Column G lines 42 - 58 has a date in them > > > > Column H lines 42 - 58 has either 1, 2, or 3 in them. > > > > The line 3 (columns I > AR have months of the year in them) > > > > I need something in 1 formula that says > > 1. IF B42="Current" and G42<=I3 TAKE H42 otherwise 0 > > 2. IF B42="EX" and G42<=I3 TAKE H42 *.7 otherwise 0 > > > > I was using a sumproduct formula but to run the same forumulas 16 times > > (lines 42 - 58) seems too long, I was wondering if there was a way of > > shortening it. > > > > Many thanks for any help > > > > If > > > >
From: john on 1 Jun 2010 07:50 On Jun 1, 2:50 pm, "Bob Phillips" <bob.phill...(a)somewhere.com> wrote: > Why not just > > =IF(G42<=I3,IF(B42="Current",H42,0),IF(B42="EX",H42*7,0)) > > But my guess is that I3:AR3 comes into it,m you justv talk about I3. > > -- > > HTH > > Bob > > "Mark D" <Ma...(a)discussions.microsoft.com> wrote in message > > news:7A25C7FD-6E23-4EA5-A10E-87F1E49398F7(a)microsoft.com... > > > > > Morning all > > > I am stuck with a forumula that I hope someone will be able to help with. > > It's quite long winded the way I am doing it but am hoping that it may be > > able to be shortened. > > > Column B Lines 42 - 58 has either "current" or "ex" in the cells > > > Column G lines 42 - 58 has a date in them > > > Column H lines 42 - 58 has either 1, 2, or 3 in them. > > > The line 3 (columns I > AR have months of the year in them) > > > I need something in 1 formula that says > > 1. IF B42="Current" and G42<=I3 TAKE H42 otherwise 0 > > 2. IF B42="EX" and G42<=I3 TAKE H42 *.7 otherwise 0 > > > I was using a sumproduct formula but to run the same forumulas 16 times > > (lines 42 - 58) seems too long, I was wondering if there was a way of > > shortening it. > > > Many thanks for any help > > > If
From: Jacob Skaria on 1 Jun 2010 07:55
Mark, try =SUMPRODUCT(((G42:G58<I3)*(B42:B58="EX")*(H42:H58*0.7))+ ((G42:G58<I3)*(B42:B58="Current")*(H42:H58)))*A1 -- Jacob (MVP - Excel) "Mark D" wrote: > Sorry Jacob my mistake. The VALUE was arising as some of the cells in H were > blank. I've changed my formula in H accordingly. > > So that works great thank you. One last additional question if I may. > > How do I wrap the whole formula to say * A1 > > I want to take the result against the % in A1 > > Thanks again > > "Jacob Skaria" wrote: > > > Do you mean? > > > > =SUMPRODUCT((G42:G58<I3)*(B42:B58="EX")*(H42:H58*0.7))+ > > SUMPRODUCT((G42:G58<I3)*(B42:B58="Current")*(H42:H58)) > > > > -- > > Jacob (MVP - Excel) > > > > > > "Mark D" wrote: > > > > > Morning all > > > > > > I am stuck with a forumula that I hope someone will be able to help with. > > > It's quite long winded the way I am doing it but am hoping that it may be > > > able to be shortened. > > > > > > Column B Lines 42 - 58 has either "current" or "ex" in the cells > > > > > > Column G lines 42 - 58 has a date in them > > > > > > Column H lines 42 - 58 has either 1, 2, or 3 in them. > > > > > > The line 3 (columns I > AR have months of the year in them) > > > > > > I need something in 1 formula that says > > > 1. IF B42="Current" and G42<=I3 TAKE H42 otherwise 0 > > > 2. IF B42="EX" and G42<=I3 TAKE H42 *.7 otherwise 0 > > > > > > I was using a sumproduct formula but to run the same forumulas 16 times > > > (lines 42 - 58) seems too long, I was wondering if there was a way of > > > shortening it. > > > > > > Many thanks for any help > > > > > > If > > > > > > |