Whats best to use Sumif, index match?

[SJC2020]

Hi, I'm using an index match but this doesn't sum all the values i need for Sales Department, i've been messing around adding SUM at the beginning of the formula but that adds everything in that column

The attachment shows the basic formula but that returns the first value it finds.

Can anybody help?

 

[AhoyNC]

Here's one way using SUMPRODUCT.

Book1
	A	B	C	D
1		Jan	Feb	Mar
2	Sales Dept	15	15	15
3	Sales Dept	20	20	20
4	IT	25	25	25
5	IT	30	30	30
6
7
8		Jan
9	Sales Dept	35
10	IT	55
Sheet2

Cell Formulas
Range	Formula
B9:B10	B9	=SUMPRODUCT(($A$2:$A$5=$A9)*($B$1:$D$1=B$8)*($B$2:$D$5))

 

[SJC2020]

Here's one way using SUMPRODUCT.

Book1
	A	B	C	D
1		Jan	Feb	Mar
2	Sales Dept	15	15	15
3	Sales Dept	20	20	20
4	IT	25	25	25
5	IT	30	30	30
6
7
8		Jan
9	Sales Dept	35
10	IT	55
Sheet2

Cell Formulas
Range	Formula
B9:B10	B9	=SUMPRODUCT(($A$2:$A$5=$A9)*($B$1:$D$1=B$8)*($B$2:$D$5))

Fantastic, thank you. Never used sumproduct before

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D
1		Jan	Feb	Mar
2	Sales Dept	15	10	15
3	Sales Dept	20	5	20
4	IT	25	5	25
5	IT	30	30	30
6
7
8		Jan	Feb
9	Sales Dept	35	15
10	It	55	35
Main

Cell Formulas
Range	Formula
B9:C10	B9	=SUM(FILTER(FILTER($B$2:$D$5,$A$2:$A$5=$A9),$B$1:$D$1=B$8))