Combining SUMIF and SUBTOTAL

[PO]

Hi

I have a table with headings in range A1:C1 and values in range A2:C10. I've
applied a autofilter on the table.

AMOUNT CURRENCY BT/FT
100 USD BT
325 EUR FT
150 EUR BT
.....


I want to sum all the values in range A2:A10 if the values in range C2:C10
are "BT".
The formula =SUMIF(C2:C10,"BT",A2:A10) works just fine.

However, I also want the formula to ignore filtered values.
=SUBTOTAL(9,A2:A10) would sum only unfiltered values, regardles however if
the value in C2:C10 is BT or not.

I.e. if I would filter the table to show only amounts in EUR the formula
should return 150 (100 should be ignored since it's value is hidden by the
filter and 325 should also be ignored since the value in column C isn'tt BT)

I've tried at least 20 combinations of SUMIF and SUBTOTAL without geting
what I want.

Is there a formula that could acomplish this or do I have to write a VBA
function?

TIA
Pete

 

[Duke Carey]

You can combine the 'filter' and the 'criteria' in a single sumproduct

=sumproduct(--(c2:c10="BT"),--(B2:b10="Eur"),a2:a10)

 

[PO]

Duke, thanks for answering.
Unfortunatly this doesn't solve my problem since the user can change the
filter. If the filter is changed to show only USD (or a combination of
different currencies) the formula doesn't work correcly.
I want the formula to sum the amount of the rows that are left visible after
the filter is applied (regardless which currency is shown) and which have
the value BT in column C.

Regards
Pete

 

[Teethless mama]

Try this:

=SUMPRODUCT(--(C6:C14="BT"),--(SUBTOTAL(3,OFFSET(A6,ROW(A6:A14)-MIN(ROW(A6:A14)),,))),A6:A14)

Adjust your range to suit

 

[Ashish Mathur]

Hi,

I may be missing something very simple but why can you filter on both BT and
EUR and then simply sum

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com