Need to combine two working formulas - not working.

  • Thread starter Thread starter shelfish
  • Start date Start date
S

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
 
Try this:

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

You can use wildcard characters with SUMIF.

Hope this helps.

Pete
 
shelfish said:
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
Click to expand...
 
Back
Top