Sumproduct odd /even cols with condition

[Paddy1979]

Hi folks,

Please can you help me with this formula.

I'm trying to sum every odd column and even column in a range with a condition that the team names match.

I have drew up an example of my issue. I hope its clear.

Many thanks.

Book1
	A	B	C	D	E	F	G
1	.
2		Summary	ATotal	MTotal
3		TEAM1
4		Shouldbe	120	51
5
6
7
8		P1A	P2M	P3A	P3M	P4A	P4M
9	TEAM1	22	21	10	8	88	22
10	TEAM2	159	118	121	112	121	22
11	TEAM3	160	119	122	113	122	23
Sheet1

 

[Richard Schollar]

Hi Patrick

You could do this in this instance with a simple Sumif:

Excel Workbook
	A	B	C	D	E	F	G
3			A	M
4			120	51
5
6
7
8		P1A	P2M	P3A	P3M	P4A	P4M
9	TEAM1	22	21	10	8	88	22
10	TEAM2	159	118	121	112	121	22
11	TEAM3	160	119	122	113	122	23
Sheet3

 

[Paddy1979]

Ah sorry Richard i need the formula to account for the team name been changed, i.e team name is a drop down validation?

 

[barry houdini]

Try this formula in C3 copied across to D3

=SUMPRODUCT((RIGHT($B8:$G8)=LEFT(C2))*($A9:$A11=$B3)*$B9:$G11)

 

[Paddy1979]

Cheers Barry. It needed a slight tweak but it works great.