Function to count visible text cells

Joe Rdz

New Member
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)

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

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

Thanks!

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

FDibbins

Well-known Member
Try this...
=SUBTOTAL(103,C2:C6)
I cannot view your file, not allowed to access file hosting sites

Joe Rdz

New Member
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

 AU AV AW AX AY AZ 2 3 Delay Family X Total 4 Past Due ATBL F AVANTE 1 5 Past Due KAXW L AVANTE 1 6 Past Due DSX1 L BLT 15 7 Past Due DSX1 L BLT 3 8 Past Due ATB2 4 OUTDOOR 97 9 Past Due ATB2 4 OUTDOOR 154 10 Past Due DSX0 L OUTDOOR 8 11 Past Due DSX0 L RELIGHT 1 12 Past Due ATB2 6 RELIGHT 2 13 Past Due ATB030 RELIGHT 1 14 Past Due ATB2 8 VOLUMETRICS 3 15 Past Due DSX1 L VOLUMETRICS 1 16 Past Due ATB0 1 VOLUMETRICS 1 17 Past Due ATB0 1 VOLUMETRICS 1 18 Past Due ATB0 2 WRAPS 4 19 Past Due ATB0 2 WRAPS 3 20 Past Due ATB0 2 WRAPS 3 21 Past Due ATB0 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>

 Cell Formula 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>

Well-known Member
Hi,

Maybe this

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

Last edited:

Joe Rdz

New Member
Hi,

Maybe this

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

This worked perfectly for what I wanted, thanks!

Replies
1
Views
450
Replies
1
Views
117
Replies
1
Views
203
Replies
9
Views
242
Replies
4
Views
125

1,127,620
Messages
5,625,902
Members
416,141
Latest member
Bartek9q

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.

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

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