Looking for the correct formula (Index, Match?)

Dtex20 · Nov 28, 2018

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]27-NOV-18[/TD]
[TD]27-Nov-18[/TD]
[TD]26-NOV-18[/TD]
[TD]26-NOV-18[/TD]
[TD]25-NOV-18[/TD]
[TD]25-NOV-18[/TD]
[TD]24-NOV-18[/TD]
[TD]24-NOV-18[/TD]
[TD]23-NOV-18[/TD]
[TD]23-NOV-18[/TD]
[TD]22-NOV-18[/TD]
[TD]22-NOV-18[/TD]
[TD]21-NOV-18[/TD]
[TD]21-NOV-18[/TD]
[TD]20-NOV-18[/TD]
[TD]20-NOV-18[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]OT IN[/TD]
[TD="align: center"]OT OUT[/TD]
[TD="align: center"]OT IN[/TD]
[TD="align: center"]OT OUT[/TD]
[TD="align: center"]OT IN[/TD]
[TD="align: center"]OT OUT[/TD]
[TD="align: center"]OT IN[/TD]
[TD]OT OUT[/TD]
[TD]OT IN[/TD]
[TD]OT OUT[/TD]
[TD]OT IN[/TD]
[TD]OT OUT[/TD]
[TD]OT IN[/TD]
[TD]OT OUT[/TD]
[TD]OT IN [/TD]
[TD]OT OUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]20[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]65[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]
[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]12[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]53[/TD]
[TD]
[/TD]
[TD]13[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]76[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Name5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]43[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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 · Nov 28, 2018

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

steve the fish · Nov 28, 2018

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 · Nov 28, 2018

steve the fish said:
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 · Nov 28, 2018

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 · Nov 28, 2018

another way with PowerQuery

Code:

[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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]PivotTable[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7]SUM N3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]OT[/td][td=bgcolor:#DDEBF7]OT IN[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td]November[/td][td]

10

[/td][/tr]
[/table]

where Attribute & OT are filters

without filters

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]PivotTable[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7](All)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]OT[/td][td=bgcolor:#DDEBF7](All)[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td]November[/td][td]

5