Subtotals with HIDDEN rows AND columns given some criteria

use below i think your one parameter is wrong
=SUMPRODUCT((SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),,1))*($B2:$F2="Pass"))) and other one is
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),,1))*($B2:$F2="Fail"))

Hello hemesh. Thanks for such a speed reply. Your proposed change brings me closer BUT the subtotals in columns G and H do not update when a column is hidden e.g. Col C. (See updated spreadsheet).

Hi,

You can do it without VBA, provided that none of your unhidden columns is less than 0.5 characters in width, which is very narrow indeed so I imagine not really a practical possibility (they may as well be hidden at that width!). In G2:

=SUMPRODUCT((CELL("width",OFFSET(B2,,N(INDEX(COLUMN(B2:F2)-MIN(COLUMN(B2:F2)),,)),,))<>0)*($B2:$F2="Pass"))

Regards

Hmmm.

Can't get it to update in Automatic Calculation mode without re-committing the formula.

Needs some sort of volatile function clause adding on to force re-calculation, though the usual suspects don't seem to work. Not sure why.

Bear with me.

Regards

Just out of interest, do you get the same behaviour in 2013, i.e. you need to re-commit the formula to get it to update with the correct result after e.g. hiding some columns?

Regards

Sorry - I'm still looking into it, but amazingly I haven't found any non-VBA ways to force a recalculation based upon a column being hidden.

Was hoping that some of the VBA experts might be able to jump in and offer some advice. Perhaps it's not even possible without VBA, though, like I said, usually adding a volatile function to the formula is a sufficient trigger. I guess Microsoft determined that even calculation of volatile functions is not to be triggered upon hiding of columns.

Cheers

Adjustments to the Width of a column (whether by hand or via hiding the column) doesn't trigger a calcluation.
So a volatile function in the formula won't help.

The only reason Row Height triggers a calculation is because of the Subtotal function.

I think they made subtotal track only rows because 'Usually', Records go in Rows. Details of each record are in columns.

It kinda goes hand in hand with the AutoFilter.
You can't filter columns either.

Originally Posted by Jonmo1

The only reason Row Height triggers a calculation is because of the Subtotal function.

I think they made subtotal track only rows because 'Usually', Records go in Rows.

Thanks, Jonmo. But surely CELL("width",... is a valid function, worthy of being recalculated when the very thing that it's designed to measure changes?!? Sure, it's nowhere near as useful as SUBTOTAL, but still...

Oh well, guess VBA is a must here, then.

Cheers

Yeah, maybe your right. Perhaps it was some sort of "afterthought" function, designed to be used occasionally to give some information at a given time about a cell, but apparently not to be used in e.g. dynamic formulas.

Oh well, thanks again for the input. At least a little bit more was learnt about this mysterious little function!

Cheers.

Originally Posted by Jonmo1

The only reason Row Height triggers a calculation is because of the Subtotal function.

In Excel versions 2007+ manually changing row height will trigger a recalculation regardless of what functions are being used.

In A1 enter: =RAND()

Now, hide and unhide row 10 and observe the result in A1.

In excel 2002 manually changing row height does not trigger a calculation. I don't have Excel 2003 to know what it does.

The formulas in this thread already have volatile functions so adding another one won't change anything.

As far as I know, manually changing column width does not trigger a calculation.