Excel formula: how to sum multiple columns on a single criteria

[TT123]

Hi,

Just wondering if anyone could give me a hand with this excel formula please?

I’m trying to pick up the Total Team A balance (TeamA_red + TeamA_yellow + TeamA_pink), the expected total is $960. I’ll need a wildcard to pick up any columns with a “TeamA” description.

I’ve tried using index match but it would only return the total of TeamA_red column ($60), unable to include TeamA_yellow and TeamA_pink.

Sum(Index($A:$F,0, Match(“*TeamA*”,$B1:$F1,0)))

Any recommendation on how to get around this please?

Thanks.

	A	B	C	D	E	F
Row 1		TeamA_red	TeamB_white	TeamA_yellow	TeamC_green	TeamA_pink		Total Team_A	$960
Row 2	Apr-24	10	20	50	40	100
Row 3	Apr-24	20	200	100	400	200
Row 4	Apr-24	30	2000	150	4000	300

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1		TeamA_red	TeamB_white	TeamA_yellow	TeamC_green	TeamA_pink		Total Team_A	$960
2	24-Apr	10	20	50	40	100
3	24-Apr	20	200	100	400	200
4	24-Apr	30	2000	150	4000	300
Sheet3

Cell Formulas
Range	Formula
I1	I1	=SUM(IF(ISNUMBER(SEARCH("TeamA",B1:F1)),B2:F4,0))

 

[Snakehips]

Or

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I
1		TeamA_red	TeamB_white	TeamA_yellow	TeamC_green	TeamA_pink		Total Team_A	$ 960
2	Apr-24	10	20	50	40	100
3	Apr-24	20	200	100	400	200
4	Apr-24	30	2000	150	4000	300
5
6
7
8
Sheet6

Cell Formulas
Range	Formula
I1	I1	=SUM(FILTER(B1:F10,LEFT(B$1:F$1,5)="TeamA"))

 

[TT123]

Thank you, both solutions work perfectly!