complex-ish formula for =SUMPRODUCT

[mrmarchuk]

Here's the formula i currently have on my 'summary' page

=SUMPRODUCT((SHEET3!F2:F31<=TODAY()-1)+0)

it works just fine and dandy but the problem is, when we add another line to SHEET2(or 3 or 4), the formula wont update, so we have to change it manually to keep checking the new line as well (it still checks f2:f31 but now there's data in f32 as well, which it doesn't check)... what i was trying to do, is make the [F31] portion auto update based on the cell F8 of the SUMMARY, here's what the formula looks like, i dont know the proper terms to get it to 'compute'... all help is appreciated

Please Login or Register  to view this content.

(The c8+1 is because there's a Header row above which the formula in C8 does not count )

I've attached a sample-copy of the spreadsheet in question:

Please Login or Register  to view this content.

[twiggywales]

not quite the right but the right kinda idea.

try .

Please Login or Register  to view this content.

that part in bold is the number of the highest row (im not sure how you got it but reading sounds like you have this in that cell)

[Pepe Le Mokko]

I'm not yet familiar with the tool but post 2003 versions have a Table option
You can declare your range as a Table and use the table's name as range
When you add rows to the Table the range adapts automatically.
Sorry I can't be more specific I only have 2003 here, but Goggle around and you'll find info

[NBVC]

If you convert Sheet2 data into a Table (via the Insert tab), then new lines added will be updated in the formula in Sheet1

[mrmarchuk]

Twiggy, that worked! thanks.
NBVC, the Table sounds like a great idea, i will have to look into it for future , it would definitely make things slightly easier than going about it the way i'm doing things

[Pepe Le Mokko]

Twiggy, that worked! thanks.
NBVC, the Table sounds like a great idea, i will have to look into it for future , it would definitely make things slightly easier than going about it the way i'm doing things

The INDIRECT and TODAY function being volatile, I would definitively have a look at the Table ooption

[Cutter]

Why would you be using SUMPRODUCT() instead of COUNTIF()?

Like this: =COUNTIF(Sheet3!F2:F31,"<="&TODAY()-1)

And instead of INDIRECT() use INDEX()

=COUNTIF(Sheet3!F2:INDEX(Sheet3!F:F,C8+1),"<="&TODAY()-1)

[mrmarchuk]

Why would you be using SUMPRODUCT() instead of COUNTIF()?

Like this: =COUNTIF(Sheet3!F2:F31,"<="&TODAY()-1)

And instead of INDIRECT() use INDEX()

=COUNTIF(Sheet3!F2:INDEX(Sheet3!F:F,C8+1),"<="&TODAY()-1)

because i'm a noob? can you explain what the plus side to using this formula vs the one i have up top is?

[Cutter]

COUNTIF() (and similar functions) is more efficient than SUMPRODUCT() when used over large ranges (or when used extensively). That means that COUNTIF() uses less CPU resources and calculates quicker.
SUMPRODUCT() and array formulas should only be used when 'normal' formulas can't do the job.
The same is true with volatile vs non-volatile functions. As Pepe Le Mokko pointed out in post #6 INDIRECT() and TODAY() functions are volatile (which means that they calculate every time anything else calculates, even when the other calculation has no bearing on them). With that in mind, they should also only be used when a non-volatile function isn't available. You're stuck with the TODAY() function but the INDIRECT() is not necessary because using the INDEX() function accomplishes the same thing.

Here is a link to read so you can better understand: http://www.decisionmodels.com/calcsecretsi.htm