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
  • 833
  • When I've done this in the past, I built a parallel table, checking for equality (or whatever condition), then embedding 1 or 0. Sum over all the entries to get a count. I'm sure there are better answers. – Jeff-Inventor ChromeOS Aug 14 '14 at 02:14
  • Noting the word VISIBLE... You will need to add a vba function to do it. There are ideas here: http://www.mrexcel.com/forum/excel-questions/45637-have-formula-consider-only-visible-rows.html – Tyson Aug 14 '14 at 02:19
  • @Tyson There are ways of doing it without VBA... One with searching: http://superuser.com/questions/683009/excel-count-number-of-visible-cells-containing-certain-string?rq=1 – BondUniverse Aug 14 '14 at 14:01
  • Ok, found a command that will work. Not sure if it can be shortened or not though... =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 Aug 14 '14 at 14:45

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
  • 22,743
  • I would prefer to not use a helper column, if I can help it. Besides, not sure how well that will work with with multiple values (High, Medium, Low - possibly more too) in column A (yours only really seems to work if only 2 values). Don't really want to have 1 column per possible value either. – BondUniverse Aug 14 '14 at 13:59
  • Why not use helper columns? They're easy to maintain and can be hidden. And yes, you might need more than one, but what's the problem? At least you (or your successor) will have no problems understanding helper columns in a year's time, whereas a formula with nested Sumproduct/Subtotal/Offset/Search will be harder to understand. If you are worried about performance: The Offset does more harm than a few helper columns. It is volatile and will cause the whole sheet to recalculate on EVERY change. – teylyn Aug 14 '14 at 21:57