Looking for the correct formula (Index, Match?)

[Dtex20]

27-NOV-18

27-Nov-18

26-NOV-18

26-NOV-18

25-NOV-18

25-NOV-18

24-NOV-18

24-NOV-18

23-NOV-18

23-NOV-18

22-NOV-18

22-NOV-18

21-NOV-18

21-NOV-18

20-NOV-18

20-NOV-18

OT IN

OT OUT

OT IN

OT OUT

OT IN

OT OUT

OT IN

OT OUT

OT IN

OT OUT

OT IN

OT OUT

OT IN

OT OUT

OT IN

OT OUT

Name1

1

20

3

65

Name2

2

5

12

21

12

Name3

4

4

2

53

13

Name4

5

76

3

Name5

1

43

6

76

31

5

12

<tbody>
</tbody>

Hi guys,

I have a table like this, except the dates are a merged cell.

I'm looking for a way to add each person OT IN & OT OUT per month.

For example Name3 OT IN - November = 10

Thanks

 

[steve the fish]

I cant see any cells that look like they are merged anywhere?

 

[steve the fish]

Anyway if your data looks like as displayed:

=SUMIFS(INDEX($B$3:$Q$7,MATCH("Name3",$A$3:$A$7,0),0),$B$2:$Q$2,"OT IN",$B$1:$Q$1,">="&DATE(2018,11,1),$B$1:$Q$1,"<"&DATE(2018,12,1))

 

[Dtex20]

I cant see any cells that look like they are merged anywhere?

Hi,

Instead of there being two 27-Nov-18... There is one in a merged cell. I had no idea how to do it on the table i created.

 

[steve the fish]

Get rid of the merged cells and do it like you did in post 1 and then you can work with the data properly.

 

[sandy666]

another way with PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"OT", type text}, {"Name1", Int64.Type}, {"Name2", Int64.Type}, {"Name3", Int64.Type}, {"Name4", Int64.Type}, {"Name5", Int64.Type}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type1", {{"Date", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Date", "OT"}, {{"SUM N1", each List.Sum([Name1]), type number}, {"SUM N2", each List.Sum([Name2]), type number}, {"SUM N3", each List.Sum([Name3]), type number}, {"SUM N4", each List.Sum([Name4]), type number}, {"SUM N5", each List.Sum([Name5]), type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"Date", "OT"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

after that use standard PivotTable from QueryTable

PivotTable
Attribute	SUM N3
OT	OT IN
Date	Value
November	10​

where Attribute & OT are filters

without filters

PivotTable
Attribute	(All)
OT	(All)
Date	Value
November	5​
	6​
	10​
	21​
	23​
	29​
	66​
	68​
	79​
	168​

 

[Dtex20]

Hi,

Thanks guys! both examples are great!

 

[sandy666]

You are welcome