Search code examples
excelexcel-2007

CountIf and Subtotal

I have a series of data columnar that I am wanting to use a countif for visible cells only that have the value 1. I can do either formula with no problem, CountIF, or SubTotal for visible cells, however when I try to combin them I get lost. Here is an example of my formula segments. =CountIf(I5:I6500,1) =SubTotal(3,I5:I6000)

How do I combine the two formulas to have a single formula that counts all instances of the number 1 in a column while not counting hidden cells due to filtering???

Solution

  • Desert Spider, This worked for me:

    =SUMPRODUCT(--(I5:I6000=(C12)),SUBTOTAL(3,OFFSET(I5,ROW(I5:I6000)-ROW(I5),0)))

OR  (the above uses another cell value to determine your query, the below uses your user input (X))

=SUMPRODUCT(--(I5:I6000="X"),SUBTOTAL(3,OFFSET(I5,ROW(I5:I6000)-ROW(I5),0)))

    Credit to:
    pgc01
    http://www.mrexcel.com/forum/excel-questions/600223-subtotal-countif.html#post2971937