# Sumproduct Formula

#### chuparfaan

##### Board Regular
Dear Experts,

I'm having a little trouble with my Sumproduct formula. The original formula below works fine. However, the way my data is set out (which unfortunately i'm not able to change) means that I now only want to look at every other cell i.e. Y10, Y12, Y14, etc.

Original Formula
=SUMPRODUCT(--(\$Y\$10:\$Y\$58="High 高"),--(\$Z\$10:\$Z\$58="Landlord 土地业权人"))

The new formula below obviously doesn't work but you can see what i'm trying to do.

New Formula
=SUMPRODUCT(--(\$Y\$10,\$Y\$12,\$Y\$14,\$Y\$16,\$Y\$18,\$Y\$20,\$Y\$22,\$Y\$24,\$Y\$26,\$Y\$28,\$Y\$30,\$Y\$32,\$Y\$34,\$Y\$36,\$Y\$38,\$Y\$40,\$Y\$42,\$Y\$44,\$Y\$46,\$Y\$48,\$Y\$50,\$Y\$52,\$Y\$54,\$Y\$56,\$Y\$58="High 高"),--(\$Z\$10,\$Z\$12,\$Z\$14,\$Z\$16,\$Z\$18,\$Z\$20,\$Z\$22,\$Z\$24,\$Z\$26,\$Z\$28,\$Z\$30,\$Z\$32,\$Z\$34,\$Z\$36,\$Z\$38,\$Z\$40,\$Z\$42,\$Z\$44,\$Z\$46,\$Z\$48,\$Z\$50,\$Z\$52,\$Z\$54,\$Z\$56,\$Z\$58="Landlord 土地业权人"))

Chuparfaan

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Dear Experts,

I'm having a little trouble with my Sumproduct formula. The original formula below works fine. However, the way my data is set out (which unfortunately i'm not able to change) means that I now only want to look at every other cell i.e. Y10, Y12, Y14, etc.

Original Formula
=SUMPRODUCT(--(\$Y\$10:\$Y\$58="High 高"),--(\$Z\$10:\$Z\$58="Landlord 土地业权人"))

The new formula below obviously doesn't work but you can see what i'm trying to do.

New Formula
=SUMPRODUCT(--(\$Y\$10,\$Y\$12,\$Y\$14,\$Y\$16,\$Y\$18,\$Y\$20,\$Y\$22,\$Y\$24,\$Y\$26,\$Y\$28,\$Y\$30,\$Y\$32,\$Y\$34,\$Y\$36,\$Y\$38,\$Y\$40,\$Y\$42,\$Y\$44,\$Y\$46,\$Y\$48,\$Y\$50,\$Y\$52,\$Y\$54,\$Y\$56,\$Y\$58="High 高"),--(\$Z\$10,\$Z\$12,\$Z\$14,\$Z\$16,\$Z\$18,\$Z\$20,\$Z\$22,\$Z\$24,\$Z\$26,\$Z\$28,\$Z\$30,\$Z\$32,\$Z\$34,\$Z\$36,\$Z\$38,\$Z\$40,\$Z\$42,\$Z\$44,\$Z\$46,\$Z\$48,\$Z\$50,\$Z\$52,\$Z\$54,\$Z\$56,\$Z\$58="Landlord 土地业权人"))

Chuparfaan
Try...
Code:
``````=SUMPRODUCT(
--(MOD(ROW(\$Y\$10:\$Y\$58)-ROW(\$Y\$10),2)=0),
--(\$Y\$10:\$Y\$58="High 高"),--(\$Z\$10:\$Z\$58="Landlord 土地业权人"))``````

Last edited:
=SUMPRODUCT(--(\$Y\$10:\$Y\$58="High 高")*--(MOD(ROW(\$Y\$10:\$Y\$58),2)=0),--(\$Z\$10:\$Z\$58="Landlord 土地业权人"))

Cant' seem to get those to work.

Ganjin with your formula i get 0 - however i can see the answer should be 3

Any further help would be much appreciated

Cant' seem to get those to work.

Ganjin with your formula i get 0 - however i can see the answer should be 3

Any further help would be much appreciated

The formula is:
Rich (BB code):
``````=SUMPRODUCT(
--(MOD(ROW(\$Y\$10:\$Y\$58)-ROW(\$Y\$10),2)=0),
--(\$Y\$10:\$Y\$58="High 高"),
--(\$Z\$10:\$Z\$58="Landlord 土地业权人"))``````

Many thanks Guys - it works now.

Now, how would i use the formula to sum the alternate rows? i.e. Y11, Y13, Y15

I also have a block of rows Y60:Y81 which i also need included in this formula.

Thanks

Chuparfaan

Many thanks Guys - it works now.

Great.

Now, how would i use the formula to sum the alternate rows? i.e. Y11, Y13, Y15
Rich (BB code):
``````=SUMPRODUCT(
--(MOD(ROW(\$Y\$10:\$Y\$58)-ROW(\$Y\$10)+1,2)=0),
--(\$Y\$10:\$Y\$58="High 高"),
--(\$Z\$10:\$Z\$58="Landlord 土地业权人"))``````

will calculate from Y11 on using every second cell.

I also have a block of rows Y60:Y81 which i also need included in this formula.
...

Not very clear. Maybe you just need to adjust the ranges to suit.

Replies
1
Views
176
Replies
1
Views
175
Replies
5
Views
212
Replies
16
Views
456
Replies
1
Views
247

1,206,971
Messages
6,075,926
Members
446,171
Latest member

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