sum non contig cells based on hidden rows

[Trish Smith]

Hi everyone,

I'm not sure that the subject line explains anything but what I would like
to do is to be able to sum a range of non-contiguous cells BUT if rows are
hidden not to sum the values in those cells.

I need to calculate scores from employees - supervisors may have 1 to 5
people reporting to them and I want to send out a generic workbook so I have
a macrobuttons on sheet 1 for them to select number of employees and this
hides the appropriate number of rows. Supervisors can then enter scores for
just those employees and most importantly calculates the score

The range is in Column H starting at Row 9 and then every other row to row
17 and cells are formatted as numbers.

There are other scores on this page not relating to individual employees and
these also have to add to the total.

At the moment the scores are summed in C45 and Averaged in D45 but I'm
guessing that the calculation will need to be done from a calculate
macrobutton.

If annyone could help that would be brilliant

Thank you

 

[Barb Reinhardt]

If you had a column that included the supervisors name, you could probably
get the result with SUMPRODUCT. Come back if you have any questions. Give
specifics of the cells with the Supervisors names, and Scores that are to be
summed.

 

[mikerickson]

The function SUBTOTAL can be used to filter out hidden cells.

 

[Rob Allchurch]

Hi everyone,

I'm not sure that the subject line explains anything but what I would like
to do is to be able to sum a range of non-contiguous cells BUT if rows are
hidden not to sum the values in those cells.

I need to calculate scores from employees - supervisors may have 1 to 5
people reporting to them and I want to send out a generic workbook so I have
a macrobuttons on sheet 1 for them to select number of employees and this
hides the appropriate number of rows. Supervisors can then enter scores for
just those employees and most importantly calculates the score

The range is in Column H starting at Row 9 and then every other row to row
17 and cells are formatted as numbers.

There are other scores on this page not relating to individual employees and
these also have to add to the total.

At the moment the scores are summed in C45 and Averaged in D45 but I'm
guessing that the calculation will need to be done from a calculate
macrobutton.

If annyone could help that would be brilliant

Thank you

Have you tried using the SUBTOTAL function? Using
=SUBTOTAL(109,A1:A10) should ignore any hidden values when calculating
the total in the range. Check out the help file for other
examples ...

Rob

 

[Trish Smith]

Hi Barb,

Thanks for answering,

I don't have those details - I've been asked to do this for another
department as a favour and what seemed like a relatively easy job has become
complicated - C'est la vie!

It also needs to be generic because it's going out to 30+ small teams and
when it needs changing I don't want to have to change lots of versions. Ok so
it would only be 5 with the different number of employees but my boss has
already lost his patience with this.

I know it's cheeky but I'd still like to see if there is a VBA solution

Many thanks

 

[Steve]

Hi Trish,
Try this function: it will count all cells in the range you select except
for the ones that are hidden:

Function SumH(sRange)
Dim iIdx As Integer
Dim iCount As Long

iCount = 0
For iIdx = 1 To sRange.Cells.Count
If Rows(sRange.Cells(iIdx).Row).Hidden = False Then
iCount = iCount + sRange.Cells(iIdx).Value
End If
Next
SumH = iCount
End Function

 

[Trish Smith]

Thanks Steve
--
Trish

Hi Trish,
Try this function: it will count all cells in the range you select except
for the ones that are hidden:

Function SumH(sRange)
Dim iIdx As Integer
Dim iCount As Long

iCount = 0
For iIdx = 1 To sRange.Cells.Count
If Rows(sRange.Cells(iIdx).Row).Hidden = False Then
iCount = iCount + sRange.Cells(iIdx).Value
End If
Next
SumH = iCount
End Function

 

[Trish Smith]

Hi Barb,

I've has a rethink and doing it differently.

Thanks for your help

 

[Trish Smith]

Thanks Rob
--
Trish

Have you tried using the SUBTOTAL function? Using
=SUBTOTAL(109,A1:A10) should ignore any hidden values when calculating
the total in the range. Check out the help file for other
examples ...

Rob