Sum values based on condition, multiple conditions

[amolvijay]

Hi Experts,
I am working on large data, where in the quarter wise summation has to be carried out for various project.
I wish to have a formula in summary table, which upon drag will return values for various projects for various quarters.

A1:E5 is the summary table and A10:N23 is actual data...

Please help

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Summary

FY21-Q1

FY21-Q2

FY21-Q3

FY21-Q4

2

Project 1

303​

303​

303​

303​

3

Project 2

3003​

3003​

3003​

3003​

4

Project 3

3006​

3006​

3006​

3006​

5

Project 4

3039​

3039​

3039​

3039​

6

7

8

9

10

FY21-Q1

FY21-Q1

FY21-Q1

FY21-Q2

FY21-Q2

FY21-Q2

FY21-Q3

FY21-Q3

FY21-Q3

FY21-Q4

FY21-Q4

FY21-Q4

11

Project 1

Rev

101​

101​

101​

101​

101​

101​

101​

101​

101​

101​

101​

101​

12

13

14

FY21-Q1

FY21-Q1

FY21-Q1

FY21-Q2

FY21-Q2

FY21-Q2

FY21-Q3

FY21-Q3

FY21-Q3

FY21-Q4

FY21-Q4

FY21-Q4

15

Project 2

Rev

1001​

1001​

1001​

1001​

1001​

1001​

1001​

1001​

1001​

1001​

1001​

1001​

16

17

18

FY21-Q1

FY21-Q1

FY21-Q1

FY21-Q2

FY21-Q2

FY21-Q2

FY21-Q3

FY21-Q3

FY21-Q3

FY21-Q4

FY21-Q4

FY21-Q4

19

Project 3

Rev

1002​

1002​

1002​

1002​

1002​

1002​

1002​

1002​

1002​

1002​

1002​

1002​

20

21

22

FY21-Q1

FY21-Q1

FY21-Q1

FY21-Q2

FY21-Q2

FY21-Q2

FY21-Q3

FY21-Q3

FY21-Q3

FY21-Q4

FY21-Q4

FY21-Q4

23

Project 4

Rev

1013​

1013​

1013​

1013​

1013​

1013​

1013​

1013​

1013​

1013​

1013​

1013​

 

[amolvijay]

Hi Experts... I got the solution...(The rows and column reference differ from example)

=SUMIFS(INDEX($D11:$Q44,MATCH($B3,$B11:$B44,0),0),INDEX($D$9:$Q$9,1,0),D$2)

 

[Peter_SSs]

Looks like you have changed the layout a bit from post #1, but this should also work for you.

=SUM(FILTER(FILTER($D$11:$Q$44,$D$9:$Q$9=C$2),$B$11:$B$44=$B3))