Excel Formula to show countif criteria from only visible cells.

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
87
Hello,

I need help with a formula to count if cell b2:b600 has the word "Alpha" when it has been filtered to display, Alpha, Bravo and Charlie. the formula i have only counts not blanks.
=SUBTOTAL(3,'Sheet1t'!B2:B600)

Thank you in advance.
 

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
87
I need it so when the cells have been filtered to give me a number of only visible cells.
Thank you though
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
Try this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >a1</td><td >TIT</td><td > </td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a2</td><td >Alpha</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a3</td><td >b</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >a5</td><td >Alpha</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >a7</td><td >b</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >=SUMPRODUCT(SUBTOTAL(103,OFFSET($B$2,ROW($B$2:$B$10)-ROW($B$2),,1))*(B2:B10="Alpha"))</td></tr></table></td></tr></table>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,359
Messages
5,339,745
Members
399,321
Latest member
ladeko

Some videos you may like

This Week's Hot Topics

Top