How to count Number of cells holding a particular value.

[Gihan Hanwella]

HI All,

In my table there are 3 columns. Each cell in the last column holds
either 1 or 0. I want to get the sum of the cells of the last column
on top of the column name. I used the sum function to do this and
works fine. But my requirement is to get the sum of the cells in the
last column when i Filter using 1st and 2nd columns. e.g. Filter using
the first column would give 10 rows from 30 rows. I need the sum of
the cells of the third row only for those 10 rows.

How can I achieve this using functions?

Thank You in advance.

Gihan.

 

[Bob Phillips]

Use SUBTOTAL(9, range)

 

[LauraGB]

Hi Gihan

The function you are looking for is SUBTOTAL. Subtotal needs to bits of
information, firstly the function number you want to perform and second the
range. If your sum function looked like =SUM(C3:C32) then replace it with
=SUBTOTAL(9,C3:C32)

The 9 is for Sum. There are a number of functions available in SUBTOTAL, eg
count, average etc, the help will list them all.

Laura GB