Lookup and Sum

[Guest]

I am trying to look up and sum multiple values using 2 different criteria. I
then want to put that sum in a cell on another sheet. I am currently using
this formula, which works fine:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8:$C$140=Project),Received!$E$8:$E$140))
(entered with ctrl+shft+entr)

The problem is that I have 2100 similar formulas on one sheet. I'd like to
write VBA code to loop through this so the average user can't mess up the
formula by simply clicking on a cell! If someone could help me determine how
to do this for one column I'm sure I could apply as needed through the rest
of the sheet. I'm not a VBA beginner but not an expert either.

The above formula is currently in cell G5 of my summary sheet. It extends
down to G173, but in every row the If True value changes columns. For
instance in G6 the formula looks like this:
=SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8:$C$140=Project),Received!$F$8:$F$140))

Would I use a loop or the Do function to run this down to G173 in my summary
sheet and out to column FE in the formula? I just need a little help getting
started! Thanks.

 

[Bob Phillips]

Do you mean something like

ColNum = 5
For i = 5 To 173
Cells(i,"G").FiormulaR1C1 =
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received!$R8C3:R140C3=Project),Received!R8C"
& ColNum & ":R140C" & ColNum & ")"
colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,
Thanks for your help. In theory this looks like it would work, but in
practice I'm getting a definition error. I'm assuming the i in "Fiormula" is
a typo but otherwise copying precisely what you have into my code.

The first time I forgot the second = and the code put the formula into each
cell but without the necessary = obviously, so it was all just text. Once I
corrected that mistake I began getting the object or application definition
error. Any ideas?

Thanks for your time,
Shannon

 

[Bob Phillips]

I left a $ sign in there

For i = 5 To 173
Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" &
_
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it
setup to run through the formulas and then copy and paste values so the user
can't see the formulas. The only problem is that it takes around 10 seconds
to process all of this. Is there an easier/quicker way?

Thanks,
Shannon

 

[Bob Phillips]

10 seconds to create the formulae, or 10 secs to recalculate.

If the former, try this


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 5 To 173
Cells(i, "G").FormulaR1C1 = _
"=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _
"(Received!R8C3:R140C3=Project),Received!R8C" & _
colNum & ":R140C" & colNum & ")"

colNum = colNum + 1
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob. It moves a lot quicker now. I appreciate all your help.

-Shannon