Function to count visible text cells

Joe Rdz

New Member
Joined
Nov 16, 2017
Messages
6
Hello,

Basically what I'm trying to do is: I have a set of different text, for example, ATB2 ATBS KAX1 2VTL, etc.
And I sorted them out by "past due" and "on time" with the given date. I applied a filter and I wanna count how many visible ATBS or KAX, etc are on "past due", I tried to use the next formula:

=COUNTIF(AV:AV, "atb*")

But it counts all the cells, including the hidden ones (On time)
view


Is there any other formula that I can try with to get to the result I want?

Screenshot here: https://goo.gl/i44Dwm

view

Thanks!:)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Try this...
=SUBTOTAL(103,C2:C6)
I cannot view your file, not allowed to access file hosting sites
 

Joe Rdz

New Member
Joined
Nov 16, 2017
Messages
6
This should be the screen, I forgot to mention, I want it to count all the text strings starting with ATB, like a wildcard with "atb*" but not sure if subtotal would be useful for that.

Sheet1

AUAVAWAXAYAZ
2
3DelayFamily XTotal
4Past DueATBL F AVANTE1
5Past DueKAXW L AVANTE1
6Past DueDSX1 L BLT15
7Past DueDSX1 L BLT3
8Past DueATB2 4 OUTDOOR97
9Past DueATB2 4 OUTDOOR154
10Past DueDSX0 L OUTDOOR8
11Past DueDSX0 L RELIGHT1
12Past DueATB2 6 RELIGHT2
13Past DueATB030 RELIGHT1
14Past DueATB2 8 VOLUMETRICS3
15Past DueDSX1 L VOLUMETRICS1
16Past DueATB0 1 VOLUMETRICS1
17Past DueATB0 1 VOLUMETRICS1
18Past DueATB0 2 WRAPS4
19Past DueATB0 2 WRAPS3
20Past DueATB0 2 WRAPS3
21Past DueATB0 2 299

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:64px;"><col style="width:95px;"><col style="width:95px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
AU4=IF(TODAY()-Z4<2,"On Time","Past Due")
AV4=MID(J4, 1, 6)
AY4=COUNTIF(AV:AV, "AV*")
AU5=IF(TODAY()-Z5<2,"On Time","Past Due")
AV5=MID(J5, 1, 6)
AY5=COUNTIF(AV:AV, "2AV*")
AU6=IF(TODAY()-Z6<2,"On Time","Past Due")
AV6=MID(J6, 1, 6)
AY6=COUNTIF(AV:AV, "2blt4*")

<tbody>
</tbody>

<tbody>
</tbody>
 

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

Maybe this

=SUMPRODUCT((ISNUMBER(SEARCH("atb*",AV:AV))*(SUBTOTAL(103,OFFSET(AV1,ROW(AV:AV)-MIN(ROW(AV:AV)),0)))))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,128,089
Messages
5,628,610
Members
416,328
Latest member
Ralph1024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top