SUM range from another sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
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)
 
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
 
Back
Top