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

[thenapolitan]

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

 

[bschwartz]

Try this:

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

 

[barry houdini]

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"))

 

[thenapolitan]

Try this:

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

Dude! Awesome! Thanks!

 

[bschwartz]

You're most welcome.

 

[thenapolitan]

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)))