Array Formula to get last occurrences

[James006]

Hello,

Currently, as shown below in Sheet1 ... a helper column generates the True/False for all the last occurrences of the combined ranges : Names and Types

Then, Column E allows to sum individual Types A,B,C ...

The objective is to do without the Helper Column C ... in a combined Array Formula


Sheet1

	A	B	C	D	E

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Names[/TD]
[TD="align: center"]Types[/TD]
[TD="align: center"]Helper[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]A[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]B[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]William[/TD]
[TD="align: center"]B[/TD]

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]Mike[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]William[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]Barry[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: center"]William[/TD]
[TD="align: center"]B[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]William[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: center"]William[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: center"]Mike[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]

</tbody>

In cell C2

=IF(SUMPRODUCT(MAX((ROW($A$2:$A$21))*($A$2:$A$21=A2)))=ROW(),1,"")

In cell E2

=SUMPRODUCT(($B$2:$B$21=D2)*($C$2:$C$21=1))

Thanks in advance for your help ...

Excel tables to the web >> Excel Jeanie HTML 4

 

[Marcelo Branco]

Why not use the helper column? It makes the creation of the formula easier and does not impair the performance.

Anyway, maybe this array formula
E2 copied down
=SUM(IF(COUNTIF(A$2:A$21,A$2:A$21)=COUNTIF(OFFSET(A$2,,,ROW(A$2:A$21)-ROW(A$2)+1),A$2:A$21),IF(B$2:B$21=D2,1)))
Ctrl+Shift+Enter

M.

 

[James006]

Hello Marcelo,

You are right ...

Just needed to test the feasibility ... of the ' All-in-One '

For a large database, it seems the helper column will be needed ..

Thanks a lot or your help ...

Cheers

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

Another possible helper formula in C2 copied down
=--(ROW(A2)=LOOKUP(2,1/(A$2:$A$1000=A2),ROW(A$2:A$1000)))

M.

 

[James006]

Marcelo,

Many thanks for your very efficient (and very kind ...) assistance.

Cheers