Dynamic range or whole columns

[Brian from Maui]

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?

 

[Aladin Akyurek]

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.

 

[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.....

 

[Aladin Akyurek]

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.

 

[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?

 

[Aladin Akyurek]

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?

 

[Brian from Maui]

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.

 

[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))

 

[Brian from Maui]

Thank-you!

Was this a typo?

=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))

 

[Aladin Akyurek]

Thank-you!

Was this a typo?

=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.