How to sum if columns have unique data.

Excelever · Jan 21, 2017

Hello ever one.

I have the below table , I was trying to sum the values in column E2 (Total COUNT) FOR matching data in column A2 and B2 ( NAME1 / NAME 2 ) and matching data in column C2 ( TYPE ).

Thank you.

( below is a sample sheet )

NAME1	NAME2	TYPE	Month	Total COUNT	COST
JOHN	HISE	110	Jan-17	22	40.3
JOHN	HISE	110	Feb-17	20	40.3
PHILIP	ROY	11N	Mar-17	22	43.3
MIKE	HON	12W	Jan-17	48	48.0
MIKE	HON	34X	Feb-17	44	44.0
ALBERT	KING	33V	Mar-17	49	49.0

Excelever · Jan 21, 2017

Aladin Akyurek said:
NAME1
NAME2
TYPE
Month
Total COUNT
COST
john

JOHN
HISE
110
17-Jan
22
40.3
hise

JOHN
HISE
110
17-Feb
20
40.3
110

PHILIP
ROY
11N
17-Mar
22
43.3

MIKE
HON
12W
17-Jan
48
48
42

MIKE
HON
34X
17-Feb
44
44

ALBERT
KING
33V
17-Mar
49
49

<tbody>
</tbody>

In I5 just enter:

=SUMIFS(E:E,A:A,I1,B:B,I2)

Is this what you are looking for?

The result is :-

1) OK , thank you.
2) But the data file I have is very big almost 3700 and has the unique NAME1 NAME 2 and also TYPE.
3) I want the results in different sheet if possible.

Do you want me to post upto 50 data set here?

Excelever · Jan 21, 2017

NAME1	NAME2	TYPE	Month	Total COUNT	COST
ABBI	DMMI	340	Jan-17	22	40.3
ABBI	DMMI	340	Feb-17	20	40.0
ABBI	DMMI	340	Mar-17	22	43.3
ABBI	JEDI	340	Jan-17	48	48.0
ABBI	JEDI	340	Feb-17	44	44.0
ABBI	JEDI	340	Mar-17	49	49.0
ABBI	RUHI	340	Jan-17	85	120.4
ABBI	RUHI	340	Feb-17	76	107.7
ABBI	RUHI	340	Mar-17	84	119.0
ADDI	JEDI	333	Jan-17	17	45.3
ADDI	JEDI	333	Feb-17	18	48.0
ADDI	JEDI	333	Mar-17	23	61.3
ADDI	JEDI	33X	Jan-17	4	10.7
ADDI	JEDI	33X	Feb-17	5	13.3
ADDI	JEDI	33X	Mar-17	1	2.7
ADDI	JEDI	33R	Mar-17	1	2.7
ADDI	RUHI	333	Jan-17	8	28.0
ADDI	RUHI	333	Feb-17	8	28.0
ADDI	RUHI	333	Mar-17	10	35.0
ADDI	RUHI	33X	Jan-17	5	17.5
ADDI	RUHI	33X	Feb-17	4	14.0
ADDI	RUHI	33X	Mar-17	4	14.0
AHBI	CAII	340	Jan-17	30	87.5
AHBI	CAII	340	Feb-17	28	81.7
AHBI	CAII	340	Mar-17	31	90.4
AHBI	CAII	399	Jan-17	1	2.9
AHBI	DMMI	340	Jan-17	98	196.0
AHBI	DMMI	340	Feb-17	88	190.7
AHBI	DMMI	340	Mar-17	97	207.0
AHBI	JEDI	340	Jan-17	296	370.0
AHBI	JEDI	340	Feb-17	268	357.3
AHBI	JEDI	340	Mar-17	295	388.8
AHBI	JEDI	399	Jan-17	30	37.5
AHBI	JEDI	399	Feb-17	28	37.3
AHBI	JEDI	399	Mar-17	31	40.8
AHBI	MEDI	340	Jan-17	13	20.6
AHBI	MEDI	340	Feb-17	12	19.0
AHBI	MEDI	340	Mar-17	13	20.6
AHBI	RUHI	340	Jan-17	304	456.0
AHBI	RUHI	340	Feb-17	276	459.9
AHBI	RUHI	340	Mar-17	313	510.8
AHBI	RUHI	32N	Jan-17	93	139.5
AHBI	RUHI	32N	Feb-17	84	140.0
AHBI	RUHI	32N	Mar-17	75	125.0
AHBI	SHWI	340	Jan-17	9	12.8
AHBI	SHWI	340	Feb-17	8	11.3
AHBI	SHWI	340	Mar-17	9	11.9
AHBI	TIFI	340	Jan-17	17	17.0
AHBI	TIFI	340	Feb-17	16	20.0
AHBI	TIFI	340	Mar-17	18	21.8
AHBI	TUUI	340	Jan-17	13	27.1
AHBI	TUUI	340	Feb-17	12	27.0
AHBI	TUUI	340	Mar-17	13	28.9

Thank you for the help.

Aladin Akyurek · Jan 21, 2017

Sill unclear where you are heading.

Let A:F of Sheet1 house the data.

Let A:C of Sheet2 house NAME1 values of interest, NAME2 values of interest, and TYPE values from row 2 downwards.

Let row 1 of Sheet2 house the relevant month/year pairs from D1 on to the right.

In D2 of Sheet2 enter, copy across, and down:

=SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2,Sheet1!$C:$C,$C2)

Is this what you are looking for?

Excelever · Jan 21, 2017

Aladin Akyurek said:
Sill unclear where you are heading.

Let A:F of Sheet1 house the data.

Let A:C of Sheet2 house NAME1 values of interest, NAME2 values of interest, and TYPE values from row 2 downwards.

Let row 1 of Sheet2 house the relevant month/year pairs from D1 on to the right.

In D2 of Sheet2 enter, copy across, and down:

=SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2,Sheet1!$C:$C,$C2)

Is this what you are looking for?

Thank you , let me try and will let you know.

Excelever · Jan 22, 2017

Excelever said:
Thank you , let me try and will let you know.

This is an excellent start and, sorry I was short in explaining what the results should be.

I did the sheets as you mentioned and got the below results which are OK except we need to refine it a bit, I am only displaying some data for comparison.

SHEET 1

NAME1	NAME2	TYPE	Month	Total COUNT	COST
ABBI	DMMI	340	17-Jan	22	40.3
ABBI	DMMI	340	17-Feb	20	40
ABBI	DMMI	340	17-Mar	22	43.3
ABBI	JEDI	340	17-Jan	48	48
ABBI	JEDI	340	17-Feb	44	44
ABBI	JEDI	340	17-Mar	49	49
ABBI	RUHI	340	17-Jan	85	120.4
ABBI	RUHI	340	17-Feb	76	107.7
ABBI	RUHI	340	17-Mar	84	119
ADDI	JEDI	333	17-Jan	17	45.3
ADDI	JEDI	333	17-Feb	18	48
ADDI	JEDI	333	17-Mar	23	61.3
ADDI	JEDI	33X	17-Jan	4	10.7
ADDI	JEDI	33X	17-Feb	5	13.3
ADDI	JEDI	33X	17-Mar	1	2.7
ADDI	JEDI	33R	17-Mar	1	2.7
ADDI	RUHI	333	17-Jan	8	28
ADDI	RUHI	333	17-Feb	8	28
ADDI	RUHI	333	17-Mar	10	35

SHEET 2

NAME1	NAME2	TYPE	17-Jan	17-Feb	17-Mar	17-Jan
ABBI	DMMI	340	64	64	64	64
ABBI	DMMI	340	64	64	64	64
ABBI	DMMI	340	64	64	64	64
ABBI	JEDI	340	141	141	141	141
ABBI	JEDI	340	141	141	141	141
ABBI	JEDI	340	141	141	141	141
ABBI	RUHI	340	245	245	245	245
ABBI	RUHI	340	245	245	245	245
ABBI	RUHI	340	245	245	245	245
ADDI	JEDI	333	58	58	58	58
ADDI	JEDI	333	58	58	58	58
ADDI	JEDI	333	58	58	58	58
ADDI	JEDI	33X	10	10	10	10
ADDI	JEDI	33X	10	10	10	10
ADDI	JEDI	33X	10	10	10	10
ADDI	JEDI	33R	1	1	1	1
ADDI	RUHI	333	26	26	26	26
ADDI	RUHI	333	26	26	26	26
ADDI	RUHI	333	26	26	26	26

Is it possible to remove the duplicated NAME1 and NAME2 only keep one NAME for one TYPE and the total.

E.G.

NAME1	NAME2	TYPE	TOTAL
ABBI	DMMI	340	64
ABBI	JEDI	340	141
ABBI	RUHI	340	245
ADDI	JEDI	333	58
ADDI	JEDI	33X	10
ADDI	JEDI	33R	1
ADDI	RUHI	333	26

Thank you again for your help.

Aladin Akyurek · Jan 22, 2017

Sheet1 (source)

Row\Col	A	B	C	D	E	F	G
1	NAME1	NAME2	TYPE	Month	Total COUNT	COST	CONCAT
2	ABBI	DMMI	340	17-Jan	22	40.3	ABBI\|DMMI\|340
3	ABBI	DMMI	340	17-Feb	20	40	ABBI\|DMMI\|340
4	ABBI	DMMI	340	17-Mar	22	43.3	ABBI\|DMMI\|340
5	ABBI	JEDI	340	17-Jan	48	48	ABBI\|JEDI\|340
6	ABBI	JEDI	340	17-Feb	44	44	ABBI\|JEDI\|340
7	ABBI	JEDI	340	17-Mar	49	49	ABBI\|JEDI\|340
8	ABBI	RUHI	340	17-Jan	85	120.4	ABBI\|RUHI\|340
9	ABBI	RUHI	340	17-Feb	76	107.7	ABBI\|RUHI\|340
10	ABBI	RUHI	340	17-Mar	84	119	ABBI\|RUHI\|340
11	ADDI	JEDI	333	17-Jan	17	45.3	ADDI\|JEDI\|333
12	ADDI	JEDI	333	17-Feb	18	48	ADDI\|JEDI\|333
13	ADDI	JEDI	333	17-Mar	23	61.3	ADDI\|JEDI\|333
14	ADDI	JEDI	33X	17-Jan	4	10.7	ADDI\|JEDI\|33X
15	ADDI	JEDI	33X	17-Feb	5	13.3	ADDI\|JEDI\|33X
16	ADDI	JEDI	33X	17-Mar	1	2.7	ADDI\|JEDI\|33X
17	ADDI	JEDI	33R	17-Mar	1	2.7	ADDI\|JEDI\|33R
18	ADDI	RUHI	333	17-Jan	8	28	ADDI\|RUHI\|333
19	ADDI	RUHI	333	17-Feb	8	28	ADDI\|RUHI\|333
20	ADDI	RUHI	333	17-Mar	10	35	ADDI\|RUHI\|333

In G2 enter and copy down:

=$A2&"|"&$B2&"|"&$C2

Sheet2 (processing)

Row\Col	A	B	C	D	E
1	7
2	IDX	NAME1	NAME2	TYPE	TOTAL
3	1	ABBI	DMMI	340	64
4	4	ABBI	JEDI	340	141
5	7	ABBI	RUHI	340	245
6	10	ADDI	JEDI	333	58
7	13	ADDI	JEDI	33X	10
8	16	ADDI	JEDI	33R	1
9	17	ADDI	RUHI	333	26
10

In A1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Sheet1!$G$2:$G$20=""),MATCH(Sheet1!$G$2:$G$20,Sheet1!$G$2:$G$20,0)),ROW(Sheet1!$G$2:$G$20)-ROW(Sheet1!$G$2)+1),1))

In A3 control+shift+enter and copy down:

=IF(ROWS($A$3:A3)>$A$1,"",SMALL(IF(FREQUENCY(IF(1-(Sheet1!$G$2:$G$20=""),MATCH(Sheet1!$G$2:$G$20,Sheet1!$G$2:$G$20,0)),ROW(Sheet1!$G$2:$G$20)-ROW(Sheet1!$G$2)+1),ROW(Sheet1!$G$2:$G$20)-ROW(Sheet1!$G$2)+1),ROWS($A$3:A3)))

In B3 just enter, copy across to D3, and down:

=IF($A3="","",INDEX(Sheet1!A$2:A$20,$A3))

In E3 just enter and copy down:

=IF($A3="","",SUMIFS(Sheet1!$E$2:$E$20,Sheet1!$G$2:$G$20,$B3&"|"&$C3&"|"&$D3))

Excelever · Jan 23, 2017

Aladin Akyurek said:
Sheet1 (source)

Row\Col

A

B

C

D

E

F

G

1
NAME1
NAME2
TYPE
Month
Total COUNT
COST
CONCAT

2
ABBI
DMMI

340

17-Jan

22

40.3
ABBI|DMMI|340

3
ABBI
DMMI

340

17-Feb

20

40
ABBI|DMMI|340

4
ABBI
DMMI

340

17-Mar

22

43.3
ABBI|DMMI|340

5
ABBI
JEDI

340

17-Jan

48

48
ABBI|JEDI|340

6
ABBI
JEDI

340

17-Feb

44

44
ABBI|JEDI|340

7
ABBI
JEDI

340

17-Mar

49

49
ABBI|JEDI|340

8
ABBI
RUHI

340

17-Jan

85

120.4
ABBI|RUHI|340

9
ABBI
RUHI

340

17-Feb

76

107.7
ABBI|RUHI|340

10
ABBI
RUHI

340

17-Mar

84

119
ABBI|RUHI|340

11
ADDI
JEDI

333

17-Jan

17

45.3
ADDI|JEDI|333

12
ADDI
JEDI

333

17-Feb

18

48
ADDI|JEDI|333

13
ADDI
JEDI

333

17-Mar

23

61.3
ADDI|JEDI|333

14
ADDI
JEDI
33X

17-Jan

4

10.7
ADDI|JEDI|33X

15
ADDI
JEDI
33X

17-Feb

5

13.3
ADDI|JEDI|33X

16
ADDI
JEDI
33X

17-Mar

1

2.7
ADDI|JEDI|33X

17
ADDI
JEDI
33R

17-Mar

1

2.7
ADDI|JEDI|33R

18
ADDI
RUHI

333

17-Jan

8

28
ADDI|RUHI|333

19
ADDI
RUHI

333

17-Feb

8

28
ADDI|RUHI|333

20
ADDI
RUHI

333

17-Mar

10

35
ADDI|RUHI|333

<tbody>
</tbody>

In G2 enter and copy down:

=$A2&"|"&$B2&"|"&$C2

Sheet2 (processing)

Row\Col

A

B

C

D

E

1

7

2
IDX
NAME1
NAME2
TYPE
TOTAL

3

1
ABBI
DMMI

340

64

4

4
ABBI
JEDI

340

141

5

7
ABBI
RUHI

340

245

6

10
ADDI
JEDI

333

58

7

13
ADDI
JEDI
33X

10

8

16
ADDI
JEDI
33R

1

9

17
ADDI
RUHI

333

26

10

<tbody>
</tbody>

In A1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Sheet1!$G$2:$G$20=""),MATCH(Sheet1!$G$2:$G$20,Sheet1!$G$2:$G$20,0)),ROW(Sheet1!$G$2:$G$20)-ROW(Sheet1!$G$2)+1),1))

In A3 control+shift+enter and copy down:

=IF(ROWS($A$3:A3)>$A$1,"",SMALL(IF(FREQUENCY(IF(1-(Sheet1!$G$2:$G$20=""),MATCH(Sheet1!$G$2:$G$20,Sheet1!$G$2:$G$20,0)),ROW(Sheet1!$G$2:$G$20)-ROW(Sheet1!$G$2)+1),ROW(Sheet1!$G$2:$G$20)-ROW(Sheet1!$G$2)+1),ROWS($A$3:A3)))

In B3 just enter, copy across to D3, and down:

=IF($A3="","",INDEX(Sheet1!A$2:A$20,$A3))

In E3 just enter and copy down:

=IF($A3="","",SUMIFS(Sheet1!$E$2:$E$20,Sheet1!$G$2:$G$20,$B3&"|"&$C3&"|"&$D3))

Wow, thanks , let me give it a try and let you know.

Excelever · Jan 24, 2017

It works , Thank you ,

but , in Sheet1 (source) I have almost 7000 rows , Sheet 2 (Processing ) only displays 21 records.

Sheet2 (processing) it only displays 21 records)

21
IDX	NAME1	NAME2	TYPE	TOTAL
1	ADDII	JEDII	333	149
10	ADDII	JEDII	33D	7
12	ADDII	JEDII	33R	1
13	ADDII	RUHII	333	87
22	ADDII	RUHII	33D	30
29	AHBII	CAIII	320	275
38	ALGII	JEDII	77H	91
47	ALGII	MEDII	77H	60
56	AMMII	JEDII	321	393
74	AMMII	JEDII	32E	79
83	AMMII	MEDII	321	78
92	AMMII	RUHII	320	40
101	AMMII	RUHII	321	157
110	AMMII	RUHII	32N	275
119	AUHI	JEDII	320	314
137	AUHI	JEDII	32E	40
146	AUHI	MEDII	320	39
155	AUHI	MEDII	32E	39
164	AUHI	RUHII	320	182
182	AUHI	RUHII	321	196
191	AUHI	RUHII	32E	40

Thank you.

Aladin Akyurek · Jan 24, 2017

Did you adjust the ranges in the formulas?

Excelever · Jan 24, 2017

Yes, I did extend the range in the array but still it does not go beyond row 21.

How to sum if columns have unique data.

Excelever

New Member

Excelever

New Member

Excel Facts

Excelever

New Member

Aladin Akyurek

MrExcel MVP

Excelever

New Member

Excelever

New Member

Aladin Akyurek

MrExcel MVP

Excelever

New Member

Excelever

New Member

Aladin Akyurek

MrExcel MVP

Excelever

New Member

Similar threads

Forum statistics

Share this page

How to sum if columns have unique data.

New Member

New Member

Excel Facts

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock