SUM range from another sheet

[CLR]

Hi All..........

I am trying to make a formula that will return the SUM of the range O:R of
the row in which is found the value in column A.......ie

=SUM("O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B))

The below formula correctly returns text of the range of interest......
="O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B))

but when I try to get the SUM of that range, all I get is #VALUE!


Any help would be appreciated........
Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Chuck,

Surely you need to INDIRECT it?

=SUM(INDIRECT("O"&MATCH(A12,'All WO''s'!$B:B,0)&":R"&MATCH(A12,'All
WO''s'!$B:B,0)))

--
---
HTH

Bob

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

 

[CLR]

Thanks Bob........I had tried that also but only got 0 as a return............

This below works, but I was shooting for something smoother.......

=IF(A12="","",VLOOKUP(A12,'All
WO''s'!$B$8:$R$65000,14,FALSE)+VLOOKUP(A12,'All
WO''s'!$B$8:$R$65000,15,FALSE)+VLOOKUP(A12,'All
WO''s'!$B$8:$R$65000,16,FALSE)+VLOOKUP(A12,'All
WO''s'!$B$8:$R$65000,17,FALSE))


Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Chuck, how about this

=SUM(N(OFFSET(B8,MATCH(A12,'All WO''s'!$B$8:$B$65000,0)-1,{13,14,15,16})))

as it is OFFSET, all the columns are reduced by 1

--
---
HTH

Bob

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

 

[CLR]

Nope, same thing....returns only 0...........Besides, copying it down would
only step down column B.........I tried modifying it but to no avail.

Thanks anyway,
Vaya con Dios,
Chuck, CABGx3