Aggregate function use with text

[RicardoCubed]

Hi all. I am trying various combinations of the Aggregate function to count the number of time the letter x appears in column that adjusts when you filter the list.
I want it to be dynamic as when i filter the list by other criteria I only want to count the filter list where x appears.
So, in the case below if I filter the list for Dept A the the result should be 1.
I would think i can replace in cell C1 the countif with an aggregate that would ignore hidden rows once filtered.
Are you able to use Aggregate function on text?
Thanks in advance for any help on this.

	A	B	C
Total
Name1
Name2
Name3
Name4
Name5
Name6
Name7
Name8
Name9
Name10
Name11

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Dept[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]B[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]C[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]D[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]B[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]C[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]D[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]D[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]B[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=COUNTIF(C2:C12,"x")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,ROW(C2:C12)-ROW(C2),0,1)),--(C2:C12="x"))

 

[RicardoCubed]

Aladin. thanks so much ! and works in the sample case i posted !
So now i am actually trying to use this approach in related scenario to cycle through a column populated with index match formulas that return an "x" if it finds the x in a column in another sheet.
Is there a way to get the same result and ignore where the index/match is not returning an x?
Hope my question makes sense and do again all your help - with this and over the years!

 

[Aladin Akyurek]

Care to elaborate a bit more? What is the formula that you have in C2 if any?

 

[RicardoCubed]

This is from the workbook that illustrates what I am trying to accomplish.
I am trying to "replace" the AU3 formula so that when I filter the data and it hides all of the index match rows that dont meet the filter criteria the AU3 formula will only count the number of occurrences that have an "x" result. The index match formula that resides in the AU column range looks to another sheet in the file as shown.

Aggregate works great with numbers but will it work the same with text?

Apologies if not clear. Maybe I have to start a brand new post and create a small sample worksheet?

	AU

<tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]perform[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]x[/TD]

</tbody>

CompCommittee​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU3[/TH]
[TD="align: left"]=COUNTIF(AU$6:AU$486,"x")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU6[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B6,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU7[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B7,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU8[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B8,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU9[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B9,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU10[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B10,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU11[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B11,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU12[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B12,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU13[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B13,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU14[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B14,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU15[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B15,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU16[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B16,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU17[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B17,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU18[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B18,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU19[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B19,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU20[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B20,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU21[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B21,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU22[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B22,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AU23[/TH]
[TD="align: left"]=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B23,Detail!$DR$4:$DR$502,0))&""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I don't get it.

In which row do you have the headers?

On which header are you applying a filter?

You seem to have this formula

=INDEX(Detail!EN$4:EN$502,MATCH(CompCommittee!$B6,Detail!$DR$4:$DR$502,0))&""

in the AU-range. What this formula return if not an x?

 

[RicardoCubed]

Sorry for not making this easier to follow.
I will create a separate illustration/example and submit new thread.
Will title it: "Aggregate Function with Text - Take 2".
Not be able to submit till perhaps tomorrow.
Again thank you for you time and interest. I have learned so much from you and this forum.
Regards - till next posting.