SUMIF from a Macro is giving a #Name error 2

[Siggy19]

My spreadsheet has two sheets and I am using a SUMIF on the second sheet to sum data with specific values from the first sheet.

Both sheets have the following Columns;

Type
Value

In the Value Column of the second sheet, my code is putting
=SUMIF('Sheet1'!A:A,RC[-1],'Sheet1'!B:B)

This LOOKS correct in the spreadsheet (in cell B2, it is correctly replacing the RC[-1] with A2) however, it is displaying a #NAME error.

The only way I have found to fix it is to double click on the cell and then click on the tick without changing anything. The formula LOOKS identical, but it somehow manages to produce the correct value, instead of the error.

Any ideas ?

 

[SkipVought]

Hi,

I don't believe you can mix A1 and R1C1 notation.

A2 works for me.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[GoodOmens]

Try this:

"=SUMIF('Sheet1'!C[-1],RC[-1],'Sheet1'!C[0])"

 

[Siggy19]

Thank you both, that worked great... <sigh>