If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct formula help (possible Index????)
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 |
#2
|
|||
|
|||
Sumproduct formula help (possible Index????)
Why not just
=IF(G42=I3,IF(B42="Current",H42,0),IF(B42="EX",H4 2*7,0)) But my guess is that I3:AR3 comes into it,m you justv talk about I3. -- HTH Bob "Mark D" wrote in message ... 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 |
#3
|
|||
|
|||
Sumproduct formula help (possible Index????)
Do you mean?
=SUMPRODUCT((G42:G58I3)*(B42:B58="EX")*(H42:H58*0 .7))+ SUMPRODUCT((G42:G58I3)*(B42:B58="Current")*(H42:H 58)) -- 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 |
#4
|
|||
|
|||
Sumproduct formula help (possible Index????)
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(G43N3,IF(B43="CURRENT",H43,0),IF(B43="EX",H43 *0.7,0))+IF(G44N3,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",H4 2*7,0)) But my guess is that I3:AR3 comes into it,m you justv talk about I3. -- HTH Bob "Mark D" wrote in message ... 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 . |
#5
|
|||
|
|||
Sumproduct formula help (possible Index????)
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:G58I3)*(B42:B58="EX")*(H42:H58*0 .7))+ SUMPRODUCT((G42:G58I3)*(B42:B58="Current")*(H42:H 58)) -- 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 |
#6
|
|||
|
|||
Sumproduct formula help (possible Index????)
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:G58I3)*(B42:B58="EX")*(H42:H58*0 .7))+ SUMPRODUCT((G42:G58I3)*(B42:B58="Current")*(H42:H 58)) -- 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 |
#7
|
|||
|
|||
Sumproduct formula help (possible Index????)
On Jun 1, 2:50*pm, "Bob Phillips" wrote:
Why not just =IF(G42=I3,IF(B42="Current",H42,0),IF(B42="EX",H4 2*7,0)) But my guess is that I3:AR3 comes into it,m you justv talk about I3. -- HTH Bob "Mark D" wrote in message ... 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 |
#8
|
|||
|
|||
Sumproduct formula help (possible Index????)
Mark, try
=SUMPRODUCT(((G42:G58I3)*(B42:B58="EX")*(H42:H58* 0.7))+ ((G42:G58I3)*(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:G58I3)*(B42:B58="EX")*(H42:H58*0 .7))+ SUMPRODUCT((G42:G58I3)*(B42:B58="Current")*(H42:H 58)) -- 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 |
Thread Tools | |
Display Modes | |
|
|