0

I have a task tracker in Excel, formatted as a table so I can filter it.

Column A in my table tracks priority (table is filtered based on status).

How can I count how many VISIBLE lines are set to "High"?

I have seen a similar question that does a search for a value within a cell, but I'm hoping for something shorter and more simple as I only care about a 100% match, not partial matches.

Ok, here's a formula that works... Not sure if it can be shortened or not. If someone can shorten it a bit and simplify it (all still without helper cells), I'll give them the credit of the answer, otherwise I may just accept this formula as the answer.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Projects!A:A,ROW(Projects!A:A)-MIN(ROW(Projects!A:A)),,1)),ISNUMBER(SEARCH("High",Projects!A:A))+0)
BondUniverse
  • 883
  • 5
  • 18
  • 27

1 Answers1

1

Use a helper column "total" with the formula

=IF([@Priority]="hi",1,0)

Then use the Total row of the table and sum the helper column. This will evaluate only the visible rows. The formula used by the Total row is the Subtotal() function with 109 as the parameter. Look it up in the help. Subtotal can do many useful things.

enter image description here

You can hide the helper column and use a formula to display the number elsewhere in the sheet using

=Table1[[#Totals],[total]]

enter image description here

teylyn
  • 23,615