Aggregate Function with Text - Take 2

R

RicardoCubed

Board Regular
Joined
Jul 10, 2013
Messages
206
Office Version
  1. 365
Platform
  1. Windows
Hi. I am looking to be able to when filtering a list on Sheet2 which is created/populated by using index/match which looks to a database contained on Sheet1 to count/sum up TEXT results ignoring hidden rows which correctly are filtered out.
Numbers work but text I am having a problem with - even when I tried the aggregate formula (which i suspect will work but I am not constructing formula correctly).

I hope the below illustrates what i would like to solve for. I filter the list in Sheet2 by department by selecting accounting in cell B4, and in cell C1 the then filtered count should be 2. Countif formula doesn't work as it doesn't ignore filtered/hidden rows in the data set. The result using aggregate in cell D1 works correctly so and I thought i could employ that for text.

Help appreciated. Thanks in advance.


ABCD
1Total of Promo47800
2
3NameDepartPromoAmount
4name1accountingx 2,000.00
5name2development0 -
6name3taxx 1,500.00
7name4accounting0 -
8name5tax0 -
9name6accountingx 3,000.00
10name7corporate0 -
11name8development0 -
12name9hrx 1,300.00
13name10corporate0 -

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
C1=COUNTIF(C4:C13,"x")
D1=AGGREGATE(9,3,D4:D13)
B4=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))
C4=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))
D4=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))
B5=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))
C5=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))
D5=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))
B6=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))
C6=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))
D6=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))
B7=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))
C7=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))
D7=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))
B8=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))
C8=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))
D8=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))
B9=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))
C9=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))
D9=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))
B10=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))
C10=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))
D10=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))
B11=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))
C11=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))
D11=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))
B12=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))
C12=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))
D12=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))
B13=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))
C13=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))
D13=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

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.
Sort by date Sort by votes
Apologies - I left out in this illustration that in some cases the index/match is returning an N/A.

So I suppose (?) I could use the previous solution from my other post by Aladin but need to exclude N/A from the formula he provided copied below.


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

Code: 
=MMULT({1,-1},SUBTOTAL({103;102},C4:C13))
 
Upvote 0
I missed the statement regarding N/A.

Code: 
=MMULT({1,-1},AGGREGATE({3;2},3,C4:C13))
 
Upvote 0
Wow. This works great! But one further question/adjusted if possible.
Is your formula able to be tweaked to adjust when I add to the end of my index/match formula &"" ? I want to do this to eliminate 0 from appearing to visually make more appealing (to me). I posted your formula in E1 and that should be 2 when filtered by accounting. I hope my question is clear.

My tweaked index/match below.
=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))&""

ABCDE
1Total of Promo4780010
2
3NameDepartPromoAmount
4name1accountingx 2,000.00
5name2development -
6name3taxx 1,500.00
7name4accounting -
8name5tax -
9name6accountingx 3,000.00
10name7corporate -
11name8development -
12name9hrx 1,300.00
13name10corporate -

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
C1=COUNTIF(C4:C13,"x")
D1=AGGREGATE(9,3,D4:D13)
E1=MMULT({1,-1},AGGREGATE({3;2},3,C4:C13))
B4=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))
C4=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))&""
D4=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))
B5=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))
C5=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))&""
D5=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))
B6=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))
C6=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))&""
D6=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))
B7=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))
C7=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))&""
D7=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))
B8=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))
C8=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))&""
D8=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))
B9=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))
C9=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))&""
D9=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))
B10=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))
C10=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))&""
D10=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))
B11=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))
C11=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))&""
D11=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))
B12=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))
C12=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))&""
D12=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))
B13=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))
C13=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))&""
D13=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
RicardoCubed said:
Apologies - I left out in this illustration that in some cases the index/match is returning an N/A.

So I suppose (?) I could use the previous solution from my other post by Aladin but need to exclude N/A from the formula he provided copied below.


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

Control+shift+enter, not just enter:

=SUM(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C12)-ROW(C2),0,1)),IF(1-ISNA(C2:C12),IF(C2:C12="x",1))))
 
Upvote 0
RicardoCubed said:
Wow. This works great! But one further question/adjusted if possible.
Is your formula able to be tweaked to adjust when I add to the end of my index/match formula &"" ? I want to do this to eliminate 0 from appearing to visually make more appealing (to me). I posted your formula in E1 and that should be 2 when filtered by accounting. I hope my question is clear.
Click to expand...
My formula relies on the fact that there are only 0 and x in the columns.
You could achieve your goal with custom number format:
Code: 
General;-General;
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
296
Tupe77
T
VinceF
  • Question
VBA To Copy & Paste Value
Replies
0
Views
147
VinceF
VinceF
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
881
felixstraube
F
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
131
SunnyAlv
SunnyAlv
S
  • Question
Xlookup on multiple columns Filtering
Replies
2
Views
142
scottishbigyin
S

Forum statistics

Threads
1,214,648
Messages
6,120,725
Members
448,987
Latest member
marion_davis

Share this page

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
Back
Top