Help with unknown row numbers in SUMIF

[jroach15]

Hi,

Can anyone tell me what I am doing wrong? I am using the following to sum data in another sheet:

=SUMIF(BOE!$B$1:$B$38,"*("&$A6&")*",BOE!$H$1:$H$38)

This gives me the correct sum but I would like to change it so that I don't have to know the absolute range, namely the row number. Here is he path I started down.

I want to swap out the "Sum Range" field of the SUMIF function above with:

BOE!H&ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0))&":H"&ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0)-ROW(BOE!$A39))




The following formula in a cell by itself gives me a value of 1

=ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0))
This one give me a value of 38:

=ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0)-ROW(BOE!$A39))

When I try to combine them in SUMIF as follows, I get an error:


=SUMIF(BOE!B&ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0))&":B"&ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0)-ROW(BOE!$A39)),"*("&$A6&")*",BOE!H&ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0))&":H"&ABS(MATCH(RIGHT(BOE!$A39,LEN(BOE!$A39)-4),BOE!A:A,0)-ROW(BOE!$A39)))

Obviously I am doing it wrong. How can I fix it?

Thanks in advanced for your help
Jim