Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining SUMIF and SUBTOTAL
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining SUMIF and SUBTOTAL
You can combine the 'filter' and the 'criteria' in a single sumproduct
=sumproduct(--(c2:c10="BT"),--(B2:b10="Eur"),a2:a10) "PO" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining SUMIF and SUBTOTAL
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 "Duke Carey" skrev i meddelandet ... You can combine the 'filter' and the 'criteria' in a single sumproduct =sumproduct(--(c2:c10="BT"),--(B2:b10="Eur"),a2:a10) "PO" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining SUMIF and SUBTOTAL
Try this:
=SUMPRODUCT(--(C6:C14="BT"),--(SUBTOTAL(3,OFFSET(A6,ROW(A6:A14)-MIN(ROW(A6:A14)),,))),A6:A14) Adjust your range to suit "PO" wrote: 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 "Duke Carey" skrev i meddelandet ... You can combine the 'filter' and the 'criteria' in a single sumproduct =sumproduct(--(c2:c10="BT"),--(B2:b10="Eur"),a2:a10) "PO" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining SUMIF and SUBTOTAL
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 "PO" <h wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining subtotal and sumif functions | Excel Worksheet Functions | |||
Subtotal and sumif help | Excel Discussion (Misc queries) | |||
subtotal and sumif | Excel Worksheet Functions | |||
Sumif & subtotal | Excel Discussion (Misc queries) | |||
Combining SUMIF and SUBTOTAL functions | Excel Worksheet Functions |