Sumproduct formula help (possible Index????)

[Mark D]

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

 

[Bob Phillips]

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.

 

[Jacob Skaria]

Do you mean?

=SUMPRODUCT((G42:G58<I3)*(B42:B58="EX")*(H42:H58*0.7))+
SUMPRODUCT((G42:G58<I3)*(B42:B58="Current")*(H42:H58))

 

[Mark D]

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

 

[Mark D]

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.

 

[Mark D]

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

 

[john]

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

 

[Jacob Skaria]

Mark, try

=SUMPRODUCT(((G42:G58<I3)*(B42:B58="EX")*(H42:H58*0.7))+
((G42:G58<I3)*(B42:B58="Current")*(H42:H58)))*A1