Help with INDIRECT & SUMIF Formula

[craigg3]

I have a SumIf Formula that works good, but I need to add INDIRECT also to keep my cell references from changing when cells get deleted.

I currently have: =SUMIF(R28:R65536, ">=1", N28:N65536) This works good.

When I try and add in the INDIRECT I cant get it to accept it. How can I get this to work?

=SUMIF(INDIRECT("R28:R65536", &">=1", & "N28:N65536"))

Thanks.

 

[Jonmo1]

You need 2 separate indirect functions, 1 for each range in the sumif

=SUMIF(INDIRECT(...),">=1",INDIRECT(...))

 

[Jonmo1]

Out of curiosity, what is in R1:R27 and N1:N27 ?

Perhaps you can just do
=SUMIF(R:R,">=1",N:N)

 

[craigg3]

You need 2 separate indirect functions, 1 for each range in the sumif

=SUMIF(INDIRECT(...),">=1",INDIRECT(...))

Thank you, this worked great.

I could not use =SUMIF(R:R,">=1",N:N) because I have other numbers in the R and N columns that do not necessarily relate to my SUMIF values.

Thanks for the help.

 

[Jonmo1]

You're welcome.

One other question, is there another column that could be used as a criteria to differenciate between cells in rows 1-27 vs rows 28+
I'm persisting on this path because INDIRECT is a volatile function, and creates excessive calculations, where the plain SUMIF would work much better.

 

[craigg3]

I could always rearrange my spreadsheet to move the non-related data to another column. I'll spend some time doing that before I finish the rest of the spreadsheet. Thank you for your assistance and recommendations.