Sum Based on the Last Entries of Each Series

[Orion19]

Hello!!

Here's a raw data sample:

Date	Program	Phase
1/1/18	Circle	PRM
1/2/18	Circle	P2
1/3/18	Circle	P3
1/1/18	Square	PRM
1/2/18	Square	P1MT
1/3/18	Square	P2
1/1/18	Triangle	P3

<tbody>
</tbody>

I'd like Excel to find the last entry for a given Program (Column B) then count which Phase it is in (Column C). The ideal output for this would be as follows:

Phase	Count
P2	1
P3	2

<tbody>
</tbody>

The last Phase for "Circle" was "P3", the last Phase for "Square" was "P2", and the last Phase for Triangle was "P3". So, "P2" received a sum of "1" while "P3" received a sum of "2". Does anyone have any ideas on how to approach that? I need to ignore all Phase entries except those that are the very last for a given program. Thank you so much in advance!!!

 

[DRSteele]

I hope this is what you're looking for.

	A	B	C	D	E
1	Date	Program	Phase
2	1/01/18	Circle	PRM
3	1/02/18	Circle	P2
4	1/03/18	Circle	P3
5	1/01/18	Square	PRM
6	1/02/18	Square	P1MT
7	1/03/18	Square	P2
8	1/01/18	Triangle	P3
9
10
11			max Date	Row	Phase of max Date
12		Circle	1/03/18	3	P3
13		Square	1/03/18	6	P2
14		Triangle	1/01/18	7	P3
15
16		Phases	Count
17		PRM	0
18		P2	1
19		P3	2
20		P1MT	0

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

Sheet22​

 

[Orion19]

Yes, exactly! The section on the bottom is the end result I'm looking for. What formulas would I need to get there? For example, how could you find the max date for Circle?

I hope this is what you're looking for.

	A	B	C	D	E
1	Date	Program	Phase
2	1/01/18	Circle	PRM
3	1/02/18	Circle	P2
4	1/03/18	Circle	P3
5	1/01/18	Square	PRM
6	1/02/18	Square	P1MT
7	1/03/18	Square	P2
8	1/01/18	Triangle	P3
9
10
11			max Date	Row	Phase of max Date
12		Circle	1/03/18	3	P3
13		Square	1/03/18	6	P2
14		Triangle	1/01/18	7	P3
15
16		Phases	Count
17		PRM	0
18		P2	1
19		P3	2
20		P1MT	0

<tbody>
</tbody>

Sheet22​

 

[DRSteele]

For some reason, the formulas did not post. here we go:

	A	B	C	D	E
1	Date	Program	Phase
2	1/01/18	Circle	PRM
3	1/02/18	Circle	P2
4	1/03/18	Circle	P3
5	1/01/18	Square	PRM
6	1/02/18	Square	P1MT
7	1/03/18	Square	P2
8	1/01/18	Triangle	P3
9
10
11			max Date	Row	Phase of max Date
12		Circle	1/03/18	3	P3
13		Square	1/03/18	6	P2
14		Triangle	1/01/18	7	P3
15
16		Phases	Count
17		PRM	0
18		P2	1
19		P3	2
20		P1MT	0

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

Sheet22​

Worksheet Formulas Cell Formula C12 =MAXIFS($A$2:$A$8,$B$2:$B$8,B12) C13 =MAXIFS($A$2:$A$8,$B$2:$B$8,B13) C14 =MAXIFS($A$2:$A$8,$B$2:$B$8,B14) E12 =INDEX($C$2:$C$8,D12) E13 =INDEX($C$2:$C$8,D13) E14 =INDEX($C$2:$C$8,D14) C17 =COUNTIFS($E$12:$E$14,B17) C18 =COUNTIFS($E$12:$E$14,B18) C19 =COUNTIFS($E$12:$E$14,B19) C20 =COUNTIFS($E$12:$E$14,B20) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D12 {=MAX(IF(MAX(IF($B$2:$B$8=B12,$A$2:$A$8))=(($B$2:$B$8=B12)*$A$2:$A$8),ROW($C$2:$C$8)-ROW($C$1)))} D13 {=MAX(IF(MAX(IF($B$2:$B$8=B13,$A$2:$A$8))=(($B$2:$B$8=B13)*$A$2:$A$8),ROW($C$2:$C$8)-ROW($C$1)))} D14 {=MAX(IF(MAX(IF($B$2:$B$8=B14,$A$2:$A$8))=(($B$2:$B$8=B14)*$A$2:$A$8),ROW($C$2:$C$8)-ROW($C$1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[Orion19]

That worked PERFECTLY. Thank you so much!!!!