ExcelBanter - countif with auto filter

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - countif with auto filter (https://www.excelbanter.com/excel-worksheet-functions/217571-countif-auto-filter.html)

countif with auto filter

I have two columns of data, and I need to find a formula that will count the
number of entries that meet certain criteria in both columns. The kicker is
that I need to be able to filter the data.
Here is what I used initially but of course it won't work when I filter one
of the columns.
=SUMPRODUCT(--(I10:I182="ind"),--(O10:O1820))

countif with auto filter

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I10:I182,ROW(I10:I18 2)-ROW(I10),0,1)),--(I10:I182="ind"),--(O10:O1820))

--
Biff
Microsoft Excel MVP

"jwells2001" wrote in message
...
I have two columns of data, and I need to find a formula that will count
the
number of entries that meet certain criteria in both columns. The kicker
is
that I need to be able to filter the data.
Here is what I used initially but of course it won't work when I filter
one
of the columns.
=SUMPRODUCT(--(I10:I182="ind"),--(O10:O1820))