# Using SUMPRODUCT with a row of discontinuous data rather than a column?

#### thenapolitan

##### Board Regular
Hey All,

I've been reading articles on mrexcel.com for quite some time, but only now am not able to come up with an answer to my question. Not sure if there is a post for this, but curious if someone could help.

Right now I am using =SUMPRODUCT(--(B5:B25>E5:E25)) to return a count when B5:B25>E5:E25. This works great.

I had to convert the columns into rows and the data is set up strangely. I can rearrange the data if necessary, but I need all the data to be in the same row.

So, I tried =SUMPRODUCT(--((J3,L3,N3,P3,R3,T3,V3,X3,Z3,AB3)>(K3,M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3))) - (can you see that the data is discontinuous?) To me, this makes two arrays with 10 items each for comparison, but that is not working.

Any ideas on how to set up something the same as =SUMPRODUCT(--(B5:B25>E5:E25)) but in rows with items that I select manually?

Thanks for any feedback you could give me.

Cheers,

Chris

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Try this:

=SUMPRODUCT(--(J3:AB3*MOD(COLUMN(J3:AB3)-1,2)>K3:AC3*MOD(COLUMN(K3:AC3),2)))

Hello Chris, welcome to MrExcel

If you have a header row with some sort of common text in the columns belonging to the first set, e.g. with "x" in each of these cells:

J2,L2,N2,P2,R2,T2,V2,X2,Z2,AB2

Then this formula will count how many of those cells are > than the cell immediately to the left

=SUMPRODUCT((J3:AB3>K3:AC3)*(J\$2:AB\$2="x"))

Try this:

=SUMPRODUCT(--(J3:AB3*MOD(COLUMN(J3:AB3)-1,2)>K3:AC3*MOD(COLUMN(K3:AC3),2)))

Dude! Awesome! Thanks!

You're most welcome.

Well, now I've come up with my next question - How would I do that same math if each of those cells are 20 different items entered in a userform?

Here are the cells being entered into the table on the button click:

myNewColumn.Range.Cells(1, 10) = tbTeam1_1Man_Game1.Text
myNewColumn.Range.Cells(1, 11) = tbTeam2_1Man_Game1.Text
myNewColumn.Range.Cells(1, 12) = tbTeam1_2Man_Game1.Text
myNewColumn.Range.Cells(1, 13) = tbTeam2_2Man_Game1.Text
myNewColumn.Range.Cells(1, 14) = tbTeam1_3Man_Game1.Text
myNewColumn.Range.Cells(1, 15) = tbTeam2_3Man_Game1.Text
myNewColumn.Range.Cells(1, 16) = tbTeam1_1Man_Game2.Text
myNewColumn.Range.Cells(1, 17) = tbTeam2_3Man_Game2.Text
myNewColumn.Range.Cells(1, 18) = tbTeam1_2Man_Game2.Text
myNewColumn.Range.Cells(1, 19) = tbTeam2_1Man_Game2.Text
myNewColumn.Range.Cells(1, 20) = tbTeam1_3Man_Game2.Text
myNewColumn.Range.Cells(1, 21) = tbTeam2_2Man_Game2.Text
myNewColumn.Range.Cells(1, 22) = tbTeam1_1Man_Game3.Text
myNewColumn.Range.Cells(1, 23) = tbTeam2_2Man_Game3.Text
myNewColumn.Range.Cells(1, 24) = tbTeam1_2Man_Game3.Text
myNewColumn.Range.Cells(1, 25) = tbTeam2_3Man_Game3.Text
myNewColumn.Range.Cells(1, 26) = tbTeam1_3Man_Game3.Text
myNewColumn.Range.Cells(1, 27) = tbTeam2_1Man_Game3.Text

myNewColumn.Range.Cells(1, 28) = tbTeam1_Doubles.Text
myNewColumn.Range.Cells(1, 29) = tbTeam2_Doubles.Text

I guess I would need to add something similar to what you gave me, but with all those variables. Seems like it could be similar logic, but not sure with all those items:

myNewColumn.Range.Cells(1, 30) = SUMPRODUCT(--(J3:AB3*MOD(COLUMN(J3:AB3)-1,2)>K3:AC3*MOD(COLUMN(K3:AC3),2)))

Replies
7
Views
440
Replies
1
Views
537
Replies
8
Views
900
Replies
1
Views
428
Replies
8
Views
221

1,220,965
Messages
6,157,122
Members
451,399
Latest member
alchavar

### 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.

### Which adblocker are you using?

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

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