Reference problem

[jesmin]

Hi There:
In my sheet1 I have:
colA colB
r1 10 5
r2 10 4
r3 15 10

I want to do a SUMIF in sheet2 at cell(1,1). I want to add sheet1 col
when value of Sheet1 colA is 10. I tried. I think I am having synte
problem
Sheets("Sheet2").cells(1,1).formular1c1="=SUMIF(Sheets("Sheet1")!A1:A3,10,Sheets("Sheet1")!B1:B3)


Would appreciate if anyone can help. Thanks in advance

 

[SteveG]

In Sheet2!

=SUMPRODUCT((Sheet1!A1:A3=10)*(Sheet1!B1:B3))

HTH

Steve

 

[Guest]

at Sheet2 - Cell A1 enter:

=SUMIF(Sheet1!$A$1:$A$3,10,Sheet1!$B$1:$B$3)

HTH

 

[jesmin]

Hi There:
In my workbook, Name of 2 worksheets are a_data & a_report. I am
getting correct result when I am using the following in a trial sheet:

Sheets("Sheet2").Cells(1,1).Formula="=SUMIF(Sheet1!A1:A3,10,Sheet1!B1:B3)".

But when I am using my original workbook code with the original name as
follows, its giving run-time error.:
Sheets("a_report").Cells(1,1).Formula="=SUMIF(a_data!A1:A3,10,a_data!B1:B3)"


--why the reference creating problem when I am chanhging just the sheet
name from Shee1 to a_data.
Thanks in advance.

 

[SteveG]

Jesmin,

Are there macros or other VB code recorded in your original that might
be causing the problem? Was the error

"Run-time error '9':
Subscript out of range"

If you have code in the original book that was recorded using the
original sheet names (i.e. Sheet!1), you'll have to update the code
with the new sheet names.

Does that help?


Steve