Sumproduct odd /even cols with condition

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
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
Joined
Apr 19, 2005
Messages
23,707
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
 

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
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
Joined
Mar 23, 2005
Messages
20,825
Try this formula in C3 copied across to D3

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

Forum statistics

Threads
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?

Disable AdBlock

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
Top