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
Total of Promo
name2development
name3tax
name4accounting
name5tax
name7corporate
name8development
name9hr
name10corporate

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

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

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

[TD="align: center"]3[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]Promo[/TD]
[TD="align: center"]Amount[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]name1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]accounting[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"] 2,000.00 [/TD]

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

[TD="align: center"]0[/TD]
[TD="align: right"] - [/TD]

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

[TD="align: center"]x[/TD]
[TD="align: right"] 1,500.00 [/TD]

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

[TD="align: center"]0[/TD]
[TD="align: right"] - [/TD]

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

[TD="align: center"]0[/TD]
[TD="align: right"] - [/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]name6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]accounting[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"] 3,000.00 [/TD]

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

[TD="align: center"]0[/TD]
[TD="align: right"] - [/TD]

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

[TD="align: center"]0[/TD]
[TD="align: right"] - [/TD]

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

[TD="align: center"]x[/TD]
[TD="align: right"] 1,300.00 [/TD]

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

[TD="align: center"]0[/TD]
[TD="align: right"] - [/TD]

</tbody>
Sheet2​

[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(C4:C13,"x")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=AGGREGATE(9,3,D4:D13)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D12[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C13[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D13[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
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
Total of Promo10
name2development
name3tax
name4accounting
name5tax
name7corporate
name8development
name9hr
name10corporate

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

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

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

[TD="align: center"]3[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Depart[/TD]
[TD="align: center"]Promo[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]name1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]accounting[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"] 2,000.00 [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]x[/TD]
[TD="align: right"] 1,500.00 [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]name6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]accounting[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"] 3,000.00 [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]x[/TD]
[TD="align: right"] 1,300.00 [/TD]
[TD="align: right"][/TD]

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

[TD="align: center"][/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[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(C4:C13,"x")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=AGGREGATE(9,3,D4:D13)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=MMULT({1,-1},AGGREGATE({3;2},3,C4:C13))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A4,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A5,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A6,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A7,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A8,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A9,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A10,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A11,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D12[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A12,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=INDEX(Sheet1!B$2:B$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C13[/TH]
[TD="align: left"]=INDEX(Sheet1!C$2:C$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))&""[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D13[/TH]
[TD="align: left"]=INDEX(Sheet1!D$2:D$11,MATCH(Sheet2!$A13,Sheet1!$A$2:$A$11,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
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 Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
308
ExcelToDAX
E
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A
N
  • Question Question
Filter values with different 8 digits
Replies
9
Views
402
Peter_SSs
Peter_SSs
E
  • Solved
Lookup the corresponding value for a match when a cell value falls between cell dates
Replies
3
Views
482
myall_blues
myall_blues
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
422
Alex Blakenburg
A

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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