Sum If or Count if help required

[nischal4487]

Hi,

I have a Column which has names to rows

Name Days
John 10
Mike 15
Mike 10
John 2
Stella 20
Stella 4
Martin 30
John 20
Mike 30

I need the result total of days as below

Name Days
John 22
Mike 55
Stella 24
Martin 30


Kindly help.

 

[arthurbr]

Try the SUMIF function
BTW your result totals do not match with the totals of your example ? e.g. John is 32 not 22

 

[nischal4487]

Try the SUMIF function
BTW your result totals do not match with the totals of your example ? e.g. John is 32 not 22

my bad , i wrote the total wrong and yes , John is 32. But is there a way i can get the table as desired and thanks for your prompt response.

 

[sandy666]

try

Name	Days		Name	Days
John	10		John	32
Mike	15		Mike	55
Mike	10		Stella	24
John	2		Martin	30
Stella	20
Stella	4
Martin	30
John	20
Mike	30

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Days", each List.Sum([Days]), type number}})
in
    Group

 

[nischal4487]

try

Name	Days		Name	Days
John	10		John	32
Mike	15		Mike	55
Mike	10		Stella	24
John	2		Martin	30
Stella	20
Stella	4
Martin	30
John	20
Mike	30

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Days", each List.Sum([Days]), type number}})
in
    Group

Thank you but can i use this query in Excel? I am bit of a starter and dunce ,kindly help.

 

[sandy666]

Thank you but can i use this query in Excel?

sure, if you've XL2010 or XL2013 with Power Query add-in or higher XL with Power Query built-in
update your profile (Account details) about Excel version and OS

 

[nischal4487]

Hi , i am using Excel 2016 , would this help?

 

[sandy666]

XL2016 has Power Query built-in so you can use M code from post #4
anyway update your profile about Excel version and OS

 

[nischal4487]

Please find screen show below , can you let me know where do i add the query?

 

[sandy666]

on your range use Ctrl+T to make it Excel Table then select any cell in this table and use From Table, in new Power Query Editor window select column Name then on Home tab click Group By
the rest in on the picture in post #8

or if it's too hard you can use SUMIF

Book1
	G	H	I	J	K
3	Name	Days		John	32
4	John	10		Mike	55
5	Mike	15		Stella	24
6	Mike	10		Martin	30
7	John	2
8	Stella	20
9	Stella	4
10	Martin	30
11	John	20
12	Mike	30
Sheet1

Cell Formulas
Range	Formula
K3:K6	K3	=SUMIF(G$4:G$12,J3:J6,H$4:H$12)

SUMIF function - Microsoft Support

How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.

support.microsoft.com