Can Excel do this?

[Tim]

Hi,

In cell F9 I have this formula: =SUMPRODUCT(--(MONTH
(M4:M100)=3),E4:E100). What I'm trying to do is
when type any number in cell C3 and run a macro or press
Enter to change the number "3" in the formula in cell F9
to the number in cell C3.
I tried next three ways with no success:

First way: Recorded this macro:
Range("C3").Select
ActiveCell.FormulaR1C1 = "2"
Range("F9").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(MONTH(R[-5]C[7]:R[91]C[7])=2),R[-
5]C[-1]:R[91]C[-1])"
End Sub
It doesn't work.

Second way: Changed formula in F9 to IF formula-works
fine with 7 if statements but it looks like Excel limits
the if formula only to 7
if statements because after the 7th if statement I
receive an error message for the formula But I need to
change "3" in the sumproduct formula
in F9 at least 12 times-for every month so this way also
doesn't do the job.

Third way : With the next Worksheet Event:

With ActiveSheet
If .Range("c3").Value = 2 Then
.Range("f9").Formula = "=SUMPRODUCT(--(MONTH
(M4:M100)=2),E4:E100)"
If .Range("c3").Value = 3 Then
.Range("f9").Formula = "=SUMPRODUCT(--(MONTH(M4:M100)
=3),E4:E100)"
....
End If
End With

It also doesn't work.

I give you the above example only with one formula in
cell F9 to keep it maximum clear and simple. In fact I
have many sumproduct formulas in
my worksheet which makes the process of the automated
updating of the months in the formulas very important and
I believe it won't be a problem for the Gurus in this
news group to manage with it.

Tim

 

[Frank Kabel]

Hi Tim
just enter the following formula in F9
=SUMPRODUCT(--(MONTH(M4:M100)=C3),E4:E100).

 

[JE McGimpsey]

I'm looking for the trap, here, but I don't see it. Is there any reason
you can't use


F9: =SUMPRODUCT(--(MONTH(M4:M100)=C3),E4:E100)

and forget about the macros?

 

[Max]

How about putting the cell ref C3 directly inside the formula?
: =SUMPRODUCT(--(MONTH(M4:M100)=C3),E4:E100)

The "3" is a hardcoded number, putting "C3" will be softcoding it
to change depending on the value in C3, which is what is desired?

 

[Tim]

Thank you All for the unbelievbly quick ansers!!!
Exactly what i needed.
Tim

 

[Max]

Glad to hear that, Tim!
Thanks for the feedback.
[ from all of us ]