Sum Visible Cells With Criteria

[jaslake]

I have a worksheet with 26 columns of financial data. ACTUAL and BUDGET for each month and year totals for ACTUAL and BUDGET. I've provided the user with the ability to hide months they don't need to see during their current session. For example, if they are working on May, they may choose to hide June through December.

A user has requested Year To Date totals for ACTUAL and BUDGET. I thought this would be relatively simple. Just add two more columns and add up the visible cells.

I got this function from the Microsoft website and it works if I don't add criteria:

Please Login or Register  to view this content.

I added criteria and tried to use it in a cell on the worksheet as follows:

=SUMIF($C$5:$Z$5,"ACTUAL",Sum_Visible_Cells($C19:$Z19))
=SUMIF($C$5:$Z$5,"BUDGET",Sum_Visible_Cells($C19:$Z19))

where $C$5:$Z$5 contains the column headers ACTUAL and BUDGET and
$C$19:$Z$19 contains line item data for ACTUAL and BUDGET.

This is not working; it generates "#VALUE!".

Any ideas on how to approach this issue?

Thanks for your help.

John

[jaslake]

I've been on the Net for 8 hours today (off and on) looking for an approach to this issue and have found nothing. Can you provide me with a direction in which to look?

Thanks, John

[DonkeyOte]

I think given you're using this Function for a very specific task you would be best served altering the Function itself along the lines of:

Please Login or Register  to view this content.

Then use it from YTD columns ... assuming YTD columns are AC (Actual) & AD (Budget) and say as per you ex. we're in row 19

AC19: =SUM_VISIBLE_CELLS($C19:$Z19,AC$5)
AD19: =SUM_VISIBLE_CELLS($C19:$Z19,AD$5)

(where AC$5 contains Actual ... and AB$5 Budget)

Note:
VBA by default is Case Sensitive - so BUDGET <> Budget. You can make insensitive either by adding:

Please Login or Register  to view this content.

at the head of the Module containing the function or by coercing the strings (criteria & column header) to a common case using UCase( ) for example.

Also always worth declaring your variables (cell, Total).

On an aside you should be aware that hiding columns won't cause the Function to recalculate automatically given it's not a Volatile action (unlike hiding rows).

[jaslake]

Thanks for your feedback. The function works well with your changes.

Yes, I make it a practice to write all my procedures Option Explicit.

I am aware of the Recalc issue, I'm just not sure how do deal with it programatically. Do you have any thoughts?

[jaslake]

The Recalc issue seems to be solved by adding

Please Login or Register  to view this content.

to each procedure that hides or shows columns.

Again, thanks for your help.

[DonkeyOte]

Re: Recalc - there's no way to invoke the calculation on Column visibility being altered manually, it's not an event that can be trapped... a Selection event invoking calculation isn't viable given it would recalculate as and when you first select the columns to un/hide but it would not recalculate on completion of the columns being hidden/unhidden unless you were to subsequently select another cell - and in reality it's then that you want the code to fire... and recalculating every time you select a cell is most definitely not the way to go. Volatile UDF's are pretty bad as it is... it's a shame MS didn't introduce horizontal Subtotal function isn't it!

EDIT: seen your last post re: VBA controlling visibility so above is moot... think about using

Please Login or Register  to view this content.

or restricting to just the range containing the UDF's.

[jaslake]

Is there a reason you are suggesting to restrict the Recalc to a range rather than the whole sheet?

John

[DonkeyOte]

Hiding the columns should really only impact the cells containing the UDF, I suspect no other formulae need recalculating.

See Charles Williams' site http://www.decisionmodels.com/calcsecrets.htm for all things XL calculation... it should be bookmarked by every developer

[jaslake]

Yes, you are correct in that.

Thank you again.

John