How to make "countif" on a filtered excel-list? i.e. like subtotal
I have a filtered list of data where one column contains either of two text
values say "A" or "B". When using filters on other columns I would like to att a counter above the list that always show the number of "A" and "B" rows in the filtered data. I do not want to add additional columns. I cannot use Countif() since that doesnt bother about the filters. If I use subtotal(3;...) it cares about the filters, but then I can only get the sum of rows containing "A"'s and "B"'s... Is this at all possible to achieve without macros or custom functions? |
How to make "countif" on a filtered excel-list? i.e. like subtotal
Stiggy,
1) Filter once on A, then again on B. 2) Use SUMPRODUCT functions with sections to replicate the filtering. =SUMPRODUCT((A1:A100 = "A")*(B1:B100 = "Stiggy")) HTH, Bernie MS Excel MVP "Stiggy" wrote in message ... I have a filtered list of data where one column contains either of two text values say "A" or "B". When using filters on other columns I would like to att a counter above the list that always show the number of "A" and "B" rows in the filtered data. I do not want to add additional columns. I cannot use Countif() since that doesnt bother about the filters. If I use subtotal(3;...) it cares about the filters, but then I can only get the sum of rows containing "A"'s and "B"'s... Is this at all possible to achieve without macros or custom functions? |
How to make "countif" on a filtered excel-list? i.e. like subtotal
Try...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R ange="A")) Hope this helps! In article , Stiggy wrote: I have a filtered list of data where one column contains either of two text values say "A" or "B". When using filters on other columns I would like to att a counter above the list that always show the number of "A" and "B" rows in the filtered data. I do not want to add additional columns. I cannot use Countif() since that doesnt bother about the filters. If I use subtotal(3;...) it cares about the filters, but then I can only get the sum of rows containing "A"'s and "B"'s... Is this at all possible to achieve without macros or custom functions? |
All times are GMT +1. The time now is 06:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com