SUM with vertical and Horizontal criteria

Sufiyan97 · Nov 3, 2021

I am using below formula with 3 criteria which works perfect, now I want to add 3 more criteria which are in column

I want to add Manager Name (Column B), Name of the Position (Column C) and Working at profile (Column E) along with the criteria in formula below

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

10/19/2021

10/20/2021

2

Prority

Manager

Name of The Position

Status

Working At Profile

Package

Openings

Interviewed

Offered

Joined

Openings

Interviewed

Offered

Joined

3

4

2

Name1

Associate/Sr. Associate

Live

Name1

18

2

0

18

1

0

5

2

Name2

Associate/Sr. Associate

Live

Name2

14

0

14

1

0

6

1

Name3

Semi Qualified CA

Live

Name3

67

0

67

0

7

2

Name4

Finance & Account Manager

Closed

Name4

1

0

1

0

8

1

Name5

Semi Qualified CA

Live

Name5

75

0

75

0

9

2

Name6

Team Leader (UK)

Live

Name6

1

0

1

0

10

2

Name7

Associate/Sr. Associate(UK)

Live

Name7

3

0

3

0

11

1

Name8

Manager (UK)

Live

Name8

12

1

Name9

Associate (Baroda)

Live

Name9

38

0

38

1

0

13

1

Name10

CA Inter(Baroda)

Closed

Name10

1

0

1

0

14

1

Name11

CA

Live

Name11

13

0

13

0

15

1

Name12

CA

Live

Name12

14

0

14

0

16

2

Name13

Trainee

Live

Name13

150

0

150

5

0

17

1

Name14

Trainee

Live

Name14

0

5

0

1

0

18

Total

395

2

0

400

8

1

0

19

Sheet1

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

October

November

2

Prority

Manager

Name of The Position

Status

Working At Profile

Package

Openings

Interviewed

Offered

Joined

Openings

Interviewed

Offered

Joined

3

4

2

Name1

Associate/Sr. Associate

Live

Name1

36

3

0

5

2

Name2

Associate/Sr. Associate

Live

Name2

28

1

0

6

1

Name3

Semi Qualified CA

Live

Name3

134

0

7

2

Name4

Finance & Account Manager

Closed

Name4

2

0

8

1

Name5

Semi Qualified CA

Live

Name5

150

0

9

2

Name6

Team Leader (UK)

Live

Name6

2

0

10

2

Name7

Associate/Sr. Associate(UK)

Live

Name7

6

0

11

1

Name8

Manager (UK)

Live

Name8

0

12

1

Name9

Associate (Baroda)

Live

Name9

76

1

0

13

1

Name10

CA Inter(Baroda)

Closed

Name10

2

0

14

1

Name11

CA

Live

Name11

26

0

15

1

Name12

CA

Live

Name12

28

0

16

2

Name13

Trainee

Live

Name13

300

5

0

17

1

Name14

Trainee

Live

Name14

18

Total

395

2

0

400

8

1

0

19

Sheet2

Cell Formulas
Range	Formula
G4:N16	G4	=SUMIFS(Sheet1!$G4:$N4,Sheet1!$G$1:$N$1,">="&DATEVALUE("1"&G$1),Sheet1!$G$1:$N$1,"<="&EOMONTH(DATEVALUE("1"&G$1),0),Sheet1!$G$2:$N$2,G$2)

bebo021999 · Nov 3, 2021

Is it mine, coming from other thread?

Do you mean, name repeats (duplicate) with different pos and different Profile, now sum it up base on name, pos and profile down the columns and month accross?
BTW, I could not see any duplicate data.
Try to repost new sample.

Sufiyan97 · Nov 3, 2021

bebo021999 said:
Is it mine, coming from other thread?

Yes, it's yours.

bebo021999 said:
Do you mean, name repeats (duplicate) with different pos and different Profile, now sum it up base on name, pos and profile down the columns and month accross?

Yes that is correct.

bebo021999 said:
BTW, I could not see any duplicate data.
Try to repost new sample.

In sample there is not duplicate data, but in real there will be

Do you need new sample?

alansidman · Nov 3, 2021

file copy of my solution is at Box for your review

If you combine row 1 and row 2 of your source data, ie. 10/19/21 Openings, etc. Then bring the data to Power Query.
Power Query Mcode below and loaded to Data Model at the end.

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Prority "] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"10/19/21Interviewed", "10/19/21 Interviewed"}, {"10/19/21Offered", "10/19/21 Offered"}, {"10/19/21Joined", "10/19/21 Joined"}, {"10/20/21Interviewed", "10/20/21 Interviewed"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Prority ", "Manager", "Name of The Position", "Status", "Working At Profile", "Package"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Attribute.1", each Date.MonthName(_), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Month Name",{{"Value", Int64.Type}})
in
    #"Changed Type1"

Once in the Data Model (Power Pivot), you can then pivot the data as below.

Book6

B

C

D

E

F

G

H

I

J

K

3

Sum of Value

Attribute.1

Attribute.2

4

October

5

Prority

Manager

Name of The Position

Status

Working At Profile

Package

Interviewed

Joined

Offered

Openings

6

1

Name10

CA Inter(Baroda)

Closed

Name10

(blank)

0

2

7

Name11

CA

Live

Name11

(blank)

0

26

8

Name12

CA

Live

Name12

(blank)

0

28

9

Name14

Trainee

Live

Name14

(blank)

0

1

5

10

Name3

Semi Qualified CA

Live

Name3

(blank)

0

134

11

Name5

Semi Qualified CA

Live

Name5

(blank)

0

150

12

Name9

Associate (Baroda)

Live

Name9

(blank)

1

0

76

13

2

Name1

Associate/Sr. Associate

Live

Name1

(blank)

3

0

36

14

Name13

Trainee

Live

Name13

(blank)

5

0

300

15

Name2

Associate/Sr. Associate

Live

Name2

(blank)

1

0

28

16

Name4

Finance & Account Manager

Closed

Name4

(blank)

0

2

17

Name6

Team Leader (UK)

Live

Name6

(blank)

0

2

18

Name7

Associate/Sr. Associate(UK)

Live

Name7

(blank)

0

6

Sheet2

bebo021999 · Nov 3, 2021

Code:

=SUMPRODUCT((Sheet1!$B$4:$B$17=$B4)*(Sheet1!$C$4:$C$17=$C4)*(Sheet1!$E$4:$E$17=$E4)*(Sheet1!$G$1:$N$1>=DATEVALUE("1"&G$1))*(Sheet1!$G$1:$N$1<="&EOMONTH(DATEVALUE("1"&G$1),0))*(Sheet1!$G$2:$N$2=G$2)*Sheet1!$G$4:$N$17)

To help you know how it works: first 3 logical statement for 3 columns, next 3 (2 for date range and 1 for job decription), last: value range need to sum up.

Sufiyan97 · Nov 9, 2021

bebo021999 said:
Code:

=SUMPRODUCT((Sheet1!$B$4:$B$17=$B4)*(Sheet1!$C$4:$C$17=$C4)*(Sheet1!$E$4:$E$17=$E4)*(Sheet1!$G$1:$N$1>=DATEVALUE("1"&G$1))*(Sheet1!$G$1:$N$1<="&EOMONTH(DATEVALUE("1"&G$1),0))*(Sheet1!$G$2:$N$2=G$2)*Sheet1!$G$4:$N$17)

To help you know how it works: first 3 logical statement for 3 columns, next 3 (2 for date range and 1 for job decription), last: value range need to sum up.

Thank you very much bebo021999
It works Great!

Sufiyan97 · Nov 9, 2021

alansidman said:
file copy of my solution is at Box for your review

If you combine row 1 and row 2 of your source data, ie. 10/19/21 Openings, etc. Then bring the data to Power Query.
Power Query Mcode below and loaded to Data Model at the end.

Power Query:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Filtered Rows" = Table.SelectRows(Source, each ([#"Prority "] <> null)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"10/19/21Interviewed", "10/19/21 Interviewed"}, {"10/19/21Offered", "10/19/21 Offered"}, {"10/19/21Joined", "10/19/21 Joined"}, {"10/20/21Interviewed", "10/20/21 Interviewed"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Prority ", "Manager", "Name of The Position", "Status", "Working At Profile", "Package"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}}), #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Attribute.1", each Date.MonthName(_), type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Month Name",{{"Value", Int64.Type}}) in #"Changed Type1"

Once in the Data Model (Power Pivot), you can then pivot the data as below.

Book6
B C D E F G H I J K
3 Sum of Value Attribute.1 Attribute.2
4 October
5 Prority Manager Name of The Position Status Working At Profile Package Interviewed Joined Offered Openings
6 1 Name10 CA Inter(Baroda) Closed Name10 (blank) 0 0 0 2
7 Name11 CA Live Name11 (blank) 0 0 0 26
8 Name12 CA Live Name12 (blank) 0 0 0 28
9 Name14 Trainee Live Name14 (blank) 0 0 1 5
10 Name3 Semi Qualified CA Live Name3 (blank) 0 0 0 134
11 Name5 Semi Qualified CA Live Name5 (blank) 0 0 0 150
12 Name9 Associate (Baroda) Live Name9 (blank) 1 0 0 76
13 2 Name1 Associate/Sr. Associate Live Name1 (blank) 3 0 0 36
14 Name13 Trainee Live Name13 (blank) 5 0 0 300
15 Name2 Associate/Sr. Associate Live Name2 (blank) 1 0 0 28
16 Name4 Finance & Account Manager Closed Name4 (blank) 0 0 0 2
17 Name6 Team Leader (UK) Live Name6 (blank) 0 0 0 2
18 Name7 Associate/Sr. Associate(UK) Live Name7 (blank) 0 0 0 6
Sheet2

Thank you alansidman for you reply

will power query work in Excel 2013?

SUM with vertical and Horizontal criteria

Sufiyan97

Well-known Member

Excel Facts

bebo021999

Well-known Member

Sufiyan97

Well-known Member

alansidman

Well-known Member

bebo021999

Well-known Member

Sufiyan97

Well-known Member

Sufiyan97

Well-known Member

Similar threads

Forum statistics

Share this page

SUM with vertical and Horizontal criteria

Sufiyan97

Well-known Member

Excel Facts

bebo021999

Well-known Member

Sufiyan97

Well-known Member

alansidman

Well-known Member

bebo021999

Well-known Member

Sufiyan97

Well-known Member

Sufiyan97

Well-known 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