Need to combine two working formulas - not working.

[shelfish]

Hi,

I'm trying to figure out how to add one more layer of complexity to my
formula, but it's not working. Any assistance would be appreciated.

My spreadsheet has several expense sections, each with a "subtotal".
I'm trying to lookup the subtotal lines and sum for the column in
which this formula lies.

Here's the simplified version:

=SUMIF (Range1, "Subtotal", Range2)

This formula works great, but people keep typing the word "Subtotal"
differently. So I'd like to replace that string with:

IF(NOT(ISERROR(FIND("subtotal",LOWER(TRIM(***CELL
ADDRESS***))))),TRUE,FALSE)

Independently, this formula also works well.

But **CELL ADDRESS*** needs to be dynamic when used in the SUMIF
function. So... how do I make it dynamic? For easy copy/pasting, the
actual working SUMIF formula is below:

=SUMIF(OFFSET($A$8,0,0,ROW()-ROW($A
$8)-1),"Subtotal:",OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),1,1),
1),-1,0,ROW($A$8)-ROW()-1,1))

Again, thanks for any help.
Shelton

 

[Pete_UK]

Try this:

=SUMIF(Range1,"*Subtotal*",Range2)

You can use wildcard characters with SUMIF.

Hope this helps.

Pete

 

[brandonjohn]

Hi,

I'm trying to figure out how to add one more layer of complexity to my
formula, but it's not working. Any assistance would be appreciated.

My spreadsheet has several expense sections, each with a "subtotal".
I'm trying to lookup the subtotal lines and sum for the column in
which this formula lies.

Here's the simplified version:

=SUMIF (Range1, "Subtotal", Range2)

This formula works great, but people keep typing the word "Subtotal"
differently. So I'd like to replace that string with:

IF(NOT(ISERROR(FIND("subtotal",LOWER(TRIM(***CELL
ADDRESS***))))),TRUE,FALSE)

Independently, this formula also works well.

But **CELL ADDRESS*** needs to be dynamic when used in the SUMIF
function. So... how do I make it dynamic? For easy copy/pasting, the
actual working SUMIF formula is below:

=SUMIF(OFFSET($A$8,0,0,ROW()-ROW($A
$8)-1),"Subtotal:",OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),1,1),
1),-1,0,ROW($A$8)-ROW()-1,1))

Again, thanks for any help.
Shelton