# Dynamic range or whole columns

This is a discussion on Dynamic range or whole columns within the Excel Questions forums, part of the Question Forums category; I have a workbook with a summary sheet and 12 worksheets for the months. In the summary sheet, one of ...

1. ## Dynamic range or whole columns

I have a workbook with a summary sheet and 12 worksheets for the months. In the summary sheet, one of the things I do is Sum and a Count function for a range that is not constant from month to month. I can't decide whether to use a dynamic range to sum or reference the whole column. Any caveats to either?

2. ## Re: Dynamic range or whole columns

No reason to worry as long as you don't need complex calculations that do not admit whole columns as reference. Although this is not sure, if Excel invokes a Used Range procedure before it feeds the reference to the formulas, whole columns would be a better option.

3. ## Re: Dynamic range or whole columns

Presently I use,

=COUNT(Mar!\$A\$3:INDEX(Mar!\$A:\$A,MATCH(9.99999999999999E+307,Mar!\$A:\$A)))

to count dates, so in this case I use a dynamic range because using a whole column reference, it counts blanks as dates. Any better formula?

Besides defining BigNum.....

4. ## Re: Dynamic range or whole columns

Originally Posted by Brian from Maui
Presently I use,

=COUNT(Mar!\$A\$3:INDEX(Mar!\$A:\$A,MATCH(9.99999999999999E+307,Mar!\$A:\$A)))

to count dates, so in this case I use a dynamic range because using a whole column reference, it counts blanks as dates. Any better formula?

Besides defining BigNum.....
=COUNT(A:A)

would not count empty cells as dates.

5. ## Re: Dynamic range or whole columns

Wrong question......it's Sunday night.

I was using Sumproduct to count a criteria by days of the week. I had,

=SUMPRODUCT(--(WEEKDAY(Jan!\$A\$3:\$A\$547)=AB\$15))

but this was conting empty cells as well. I don't know the range, so after every month so far, I adjust the range, but ended up with,

=SUMPRODUCT(--(WEEKDAY(Jan!\$A\$3:\$A\$547)=AB\$15),--(Jan!\$A\$3:\$A\$547<>""))

hoping it doesn't go past row 547.

Any better suggestion for a dynamic range?

6. ## Re: Dynamic range or whole columns

Originally Posted by Brian from Maui

Wrong question......it's Sunday night.

I was using Sumproduct to count a criteria by days of the week. I had,

=SUMPRODUCT(--(WEEKDAY(Jan!\$A\$3:\$A\$547)=AB\$15))

but this was conting empty cells as well. I don't know the range, so after every month so far, I adjust the range, but ended up with,

=SUMPRODUCT(--(WEEKDAY(Jan!\$A\$3:\$A\$547)=AB\$15),--(Jan!\$A\$3:\$A\$547<>""))

hoping it doesn't go past row 547.

Any better suggestion for a dynamic range?
What is the smallest, permissible date that you can have?

7. ## Re: Dynamic range or whole columns

Originally Posted by Aladin Akyurek

What is the smallest, permissible date that you can have?
I have a worksheet for each month, so the first of every month is the smallest for each sheet.

Edit

Only concerned with the current year.

8. ## Re: Dynamic range or whole columns

Originally Posted by Brian from Maui
Originally Posted by Aladin Akyurek

What is the smallest, permissible date that you can have?
I have a worksheet for each month, so the first of every month is the smallest for each sheet.

Edit

Only concerned with the current year.
Define BigNum.

Activate, this is essential, the Jan sheet. And define Jan!DateRange as referring to:

=Jan!\$A\$3:INDEX(Jan!\$A:\$A,MATCH(BigNum,Jan!\$A:\$A))

Activate, this is essential, the Feb sheet. And define Feb!DateRange as referring to:

=Feb!\$A\$3:INDEX(Jan!\$A:\$A,MATCH(BigNum,Feb!\$A:\$A))

Repeat the foregoing for the rest of the month sheets.

Now you can have:

=SUMPRODUCT(--(WEEKDAY(Jan!DateRange)=AB\$15),--ISNUMBER(Jan!DateRange))

or, if you fear invalid dates...

=SUMPRODUCT(--(WEEKDAY(Jan!DateRange)=AB\$15),--(TEXT(Jan!DateRange,"000000")>=37987))

9. ## Re: Dynamic range or whole columns

Thank-you!

=Feb!\$A\$3:INDEX(Jan!\$A:\$A,MATCH(BigNum,Feb!\$A:\$A))

did you mean,

=Feb!\$A\$3:INDEX(Feb!\$A:\$A,MATCH(BigNum,Feb!\$A:\$A))

10. ## Re: Dynamic range or whole columns

Originally Posted by Brian from Maui
Thank-you!

=Feb!\$A\$3:INDEX(Jan!\$A:\$A,MATCH(BigNum,Feb!\$A:\$A))

did you mean,

=Feb!\$A\$3:INDEX(Feb!\$A:\$A,MATCH(BigNum,Feb!\$A:\$A))
Yes, of course.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•