A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sumproduct formula help (possible Index????)



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 10:38 AM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default 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  
Old June 1st, 2010, 10:50 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default 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  
Old June 1st, 2010, 11:46 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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  
Old June 1st, 2010, 12:07 PM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default 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  
Old June 1st, 2010, 12:19 PM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default 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  
Old June 1st, 2010, 12:37 PM posted to microsoft.public.excel.worksheet.functions
Mark D
external usenet poster
 
Posts: 62
Default 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  
Old June 1st, 2010, 12:50 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
external usenet poster
 
Posts: 1
Default 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  
Old June 1st, 2010, 12:55 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.