sumif confusion pt.2 - how to copy?

  • Thread starter Thread starter Shelly
  • Start date Start date
S

Shelly

Of course moments after I posted sumif confusion, the
formula worked.

I want to copy the formula from cell b2 to cells b3:b30
without having the cell reference change. I can easily go
in and change ,2, to ,3, etc. but how do I prevent the
address from automatically changing from $O10 to O11?

this works:

=SUMIF('2-94568'!$O10:$O82,2,'2-94568'!$H10:$H82)this
works

when I copy it to the lower cell, it comes out like this:
=SUMIF('2-94568'!$O11:$O83,2,'2-94568'!$H11:$H83)

Thanks very much.
Shelly
 
Shelly said:
Of course moments after I posted sumif confusion, the
formula worked.

I want to copy the formula from cell b2 to cells b3:b30
without having the cell reference change. I can easily go
in and change ,2, to ,3, etc. but how do I prevent the
address from automatically changing from $O10 to O11?

this works:

=SUMIF('2-94568'!$O10:$O82,2,'2-94568'!$H10:$H82)this
works

when I copy it to the lower cell, it comes out like this:
=SUMIF('2-94568'!$O11:$O83,2,'2-94568'!$H11:$H83)

Thanks very much.
Shelly
Click to expand...

Put a $ sign before the row numbers:
=SUMIF('2-94568'!$O$10:$O$82,2,'2-94568'!$H$10:$H$82)
 
Hi Shelly!

This seems to work OK as a formula that can be copied down and across:

=SUMIF(INDIRECT("'"&B$1&"'!$B$2:$B$7"),Sheet4!$A2,INDIRECT("'"&B$1&"'!
$A$2:$A$7"))

Look very carefully at the quotation marks! First in INDIRECT is " '
" the second is " '

INDIRECT allows you to build cell references indirectly and in this
case I'm taking the column heads to build the sheet name.

Also note the careful use of the $ signs to ensure copying down and
across continues to "look" at the header row and header column.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top