Count Based on Total With Criteria

[kumara_faith]

Hi,

I have the following table:

Name	Branch	Jan	Feb	Mar	Total
Alex	Texas	10	5	8	23
	Washington	5	5	8	18
Mary	Hawaii	5	5	8	18
	Virginia	10	5	8	23

I am trying to summarize the data based as per the table below:

Name	Branch	Jan	Feb	Mar
Alex	Texas	15	10	16
Mary	Virginia	15	10	16

This is based on the following criteria:

1) The branch name for each staff is populated based on the highest total. Example for Alex, the total for Texas is 23 and Washington is 18. Therefore all the total for Jan to Mar is placed into Texas for each month
2) The same principle applies even if there is more than two states for each staff.

Is there a way to use a formula to achieve this ?

Appreciate all the help.

 

[C Moore]

Power query could solve this fairly easily. It would take learning some new skills though.

 

[JamesCanale]

This is much easier if the name is in every row. So I made a helper column if that is not practical.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G
1	Name	Branch	Jan	Feb	Mar	Total	Helper Column
2	Alex	Texas	10	5	8	23	Alex
3		Washington	5	5	8	18	Alex
4	Mary	Hawaii	5	5	8	18	Mary
5		Virginia	10	5	8	23	Mary
6
7	Alex	Texas	15	10	16
8	Mary	Virginia	15	10	16
Sheet2

Cell Formulas
Range	Formula
F2:F5	F2	=SUM(C2:E2)
G2:G5	G2	=IF(A2="",A1,A2)
A7:A8	A7	=UNIQUE(G2:G5)
B7:B8	B7	=INDEX(FILTER(B$2:B$5,$G$2:$G$5=$A7),MATCH(MAX(FILTER(F$2:F$5,$G$2:$G$5=$A7)),FILTER(F$2:F$5,$G$2:$G$5=$A7),0))
C7:E8	C7	=SUM(FILTER(C$2:C$5,$G$2:$G$5=$A7))
Dynamic array formulas.

 

[kumara_faith]

Hi James,

Thank you for your response.

The actual list is really long so it will be challenging to add the helper column and add the names manually. Is there a way formularize this without the helper column ?

 

[JamesCanale]

The helper column is a formula (see below) - so it aughtn't be too difficult to put that in. I've been trying to figure a way to output an array filling in the blanks from above, but I've had no luck.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G
1	Name	Branch	Jan	Feb	Mar	Total	Helper Column
2	Alex	Texas	10	5	8	23	Alex
3		Washington	5	5	8	18	Alex
4		NY	1	1	1	3	Alex
5	Mary	Hawaii	5	5	8	18	Mary
6		Virginia	10	5	8	23	Mary
Sheet2

Cell Formulas
Range	Formula
F2:F6	F2	=SUM(C2:E2)
G2:G6	G2	=IF(A2="",G1,A2)

 

[kumara_faith]

Hi James,

Thank you for your response and appreciate it.