CONDITIONAL SUBTOTALS

[FARAZ QURESHI]

How can u calculate a conditional subtotal?

Some array formula like:

{=SUBTOTAL(3,(IF((A1:A5=1)*(B1:B5="a"),C1:C5)))}

In other words, to count elements within C1:C5, only if any of the
respective cels in A1:A5 reflect 1 and B1:B5 has "a"?

Thanx

 

[Tyro]

I'm not exactly sure what you want, since you give no example.

Perhaps =SUMPRODUCT((A1:A5=1)*(B1:B5="a")*C1:C5)

Tyro

 

[T. Valko]

Do you want a conditional *count* or a conditional *sum* ?

I'm assuming this is on *filtered* data.

If you want a conditional *count* then column C is irrelavent (unless you
need to test column C for empty/blank cells). You would just count based on:

(A1:A5=1)*(B1:B5="a")

However, if you want a conditional *sum* then column C is in play!

Here's a formula for a conditional *count* based on A = 1, B = A

Data range is row 2 to row 10:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),--(A2:A10=1),--(B2:B10="A"))

 

[FARAZ QURESHI]

Thanx 4 the reply Tyro,

I am quite familiar with the SUMPRODUCT function's extensive usage. However,
I want to use the SUBTOTAL function so as to have the amounts, average or
number of only VISIBLE cases when data is filtered, provided the appropriate
conditions meet.

Another example:

A B C
1 REGION CLASS QTY
2 NORTH CLASS A 3,000
3 SOUTH CLASS A 4,000
4 NORTH CLASS B 5,000
5 SOUTH CLASS A 6,000
6 SOUTH CLASS B 2,000

I want a subtotal type of formula at C7 so that when Rows 2:5 are filtered
on any basis the result in C7 is the total of only "VISIBLE" cells in column
C which have SOUTH in column A and CLASS A in column B.

--

Best Regards,
FARAZ A. QURESHI

I'm not exactly sure what you want, since you give no example.

Perhaps =SUMPRODUCT((A1:A5=1)*(B1:B5="a")*C1:C5)

Tyro

 

[T. Valko]

So, you want a conditional *sum* ?

Just need a slight modification to the formula I posted in my other reply:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),--(A2:A10=1),--(B2:B10="A"),C2:C10)


--
Biff
Microsoft Excel MVP

Thanx 4 the reply Tyro,

I am quite familiar with the SUMPRODUCT function's extensive usage.
However,
I want to use the SUBTOTAL function so as to have the amounts, average or
number of only VISIBLE cases when data is filtered, provided the
appropriate
conditions meet.

Another example:

A B C
1 REGION CLASS QTY
2 NORTH CLASS A 3,000
3 SOUTH CLASS A 4,000
4 NORTH CLASS B 5,000
5 SOUTH CLASS A 6,000
6 SOUTH CLASS B 2,000

I want a subtotal type of formula at C7 so that when Rows 2:5 are filtered
on any basis the result in C7 is the total of only "VISIBLE" cells in
column
C which have SOUTH in column A and CLASS A in column B.