1

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???

pnuts
  • 58,317
  • 11
  • 87
  • 139
Desert Spider
  • 744
  • 1
  • 13
  • 31
  • The column with the 1's & 0's is not the primary column I will be filtering by. There are other columns with Region, Office nbr, etc. that will be the primary filter. – Desert Spider May 09 '13 at 18:09

2 Answers2

1

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

MrsAdmin
  • 548
  • 5
  • 12
  • 34
0

Filter on ColumnI (as well) to select 1 and the count should appear at the bottom left of your screen (just to the right of Ready, so no formula may be needed.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thank you for your comment. I am aware of the auto summary information in excel and that is not providing what I am looking for. The auto summary does not grant the conditional counting that I am looking for. – Desert Spider May 11 '13 at 12:41