Sumproduct formula help (possible Index????)
in [Functions]
|
Prev: If OR formula
Next: Multi-Condition Formula
From: Mark D on 1 Jun 2010 05:38 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: Bob Phillips on 1 Jun 2010 05:50 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" <MarkD(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 06:46 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: Mark D on 1 Jun 2010 07:07 Hi Bob, Thanks for the reply. I used the formula you provided but am not getting the result I require. For example I have run the forumla to cover both a ''CURRENT'' result and an ''EX22 result. Summary of the cells are B43 = CURRENT B44 = EX G43 = 28/09/2006 G44 = 30/12/2008 H43 = 2 H44 = 2 N3 = 30/06/2010 This is the formula I have used =IF(G43<N3,IF(B43="CURRENT",H43,0),IF(B43="EX",H43*0.7,0))+IF(G44<N3,IF(B44="CURRENT",H44,0),IF(B44="EX",H44*0.7,0)) The answer I require from the above would be 3.4 (the current = 2 and the ex =2*0.7) But it's returning 2 Thanks again "Bob Phillips" 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" <MarkD(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: Mark D on 1 Jun 2010 07:19
Hi Jacob for some reason I am getting ''VALUE'' come up when i enter this formula. But I think what you are saying is right. Just not sure why I am getting a VALUE come up. Have checked the forumla through a couple times. "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 > > > > |