# Sumproduct odd /even cols with condition

##### Well-known Member
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
ABCDEFG
1.
2SummaryATotalMTotal
3TEAM1
4Shouldbe12051
5
6
7
8P1AP2MP3AP3MP4AP4M
9TEAM122211088822
10TEAM215911812111212122
11TEAM316011912211312223
Sheet1

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Richard Schollar

##### MrExcel MVP
Hi Patrick

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

Excel Workbook
ABCDEFG
3AM
412051
5
6
7
8P1AP2MP3AP3MP4AP4M
9TEAM122211088822
10TEAM215911812111212122
11TEAM316011912211312223
Sheet3

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

##### MrExcel MVP
Try this formula in C3 copied across to D3

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

##### Well-known Member
Cheers Barry. It needed a slight tweak but it works great.

Replies
4
Views
320
Replies
9
Views
556
Replies
7
Views
377
Replies
4
Views
392
Replies
1
Views
947

1,181,055
Messages
5,927,858
Members
436,573
Latest member
CMR237

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back