SUMIF criteria across dynamic range

**Journeyman3000** · 03-03-2014, 04:49 PM

Hi all,

I'm looking for a way to write this:

Please Login or Register  to view this content.

as this:

Please Login or Register  to view this content.

Where the criteria runs across a range (G to BG) that can sometimes have columns deleted, but the range must remain fixed.

Cheers

**Tony Valko** · 03-03-2014, 04:53 PM

Like this...

=SUMIF(INDIRECT("Sheet1!G4:BG4"),Sheet2!$D6,INDIRECT("Sheet1!G306:BG306"))

That formula will always refer to Sheet1!G4:BG4 and Sheet1!G306:BG306.

**Journeyman3000** · 03-03-2014, 05:00 PM

Thanks Tony.

I didn't think about indirect in this way, but seems to work just fine. Just to clarify, it shouldn't update the function if I delete columns?

Cheers and take a rep for quick response.

**Tony Valko** · 03-03-2014, 05:10 PM

Originally Posted by Journeyman3000

Just to clarify, it shouldn't update the function if I delete columns?

Nope. The formula will ALWAYS refer to Sheet1!G4:BG4 and Sheet1!G306:BG306.

Cheers and take a rep for quick response.

Thanks!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.