SUM of certain cells after Autofilter

  • Thread starter Thread starter Bernd
  • Start date Start date
B

Bernd

Hallo,

The following worksheet (as an excerpt of a bigger one) is to be analysed:
x x 2
x 1
x 2
2

After Autofilter regarding x in any column the sum of VISIBLE cells with
the value 2 should be calculated (e.g. 2 for the first column and 4 for
the second one). The function SUBTOTAL seems not to give a proper result
considering conditions). Any idea?

Regards

Bernd
 
Hi Bernd

Where are you putting the SUBTOTAL formula? Is it within the range of
data and therefore possibly getting confused?
Try inserting a row above your first row and putting the formula there.
=SUBTOTAL(9,C2:C1000)
adjhust range to suit
 
Hi Roger

The problem is to consider a criteria ("2") in addition to the filter
(marking for a certain department).

For better understanding: the sum is required for 1, 2, 3,... On the
basis of these results, a diagramm will be built.

There is little opportunity to reorganize the sheet or to create
separate areas for assistance because it's not "my" worksheet.

Bernd
 
Hi Bernd

I think I now undestand what you are trying to do. We do not need filters or
subtotals at all.

Try
=SUMPRODUCT(--($A$1:$A$100="X"),--($C$1:$C$100=2))*2 for example

If the criterion of 2 or 1 or 3 were held in another cell say E1 then it
could be generalised to
=SUMPRODUCT(--($A$1:$A$100="X"),--($C$1:$C$100=E1))*E1
=SUMPRODUCT(--($B$1:$B$100="X"),--($C$1:$C$100=E1))*E1
 
ok, but depending on selection of the user once the rows with "x" in the
first columns must be calculated, another time these with an "x" in the
second (ans so on) column
 
Hi Bernd

One way would be set up named ranges e.g.
RangeA=A1:A100, RangeB=B1:B100, Data=C1:C100

Then with criteria 1,2,3 etc in E1, Range letter A,B, etc in F1
=SUMPRODUCT(--(INDIRECT("range"&F1)="X"),--(Data=E1))*E1
 
Back
Top