Sumproduct Formula

chuparfaan

Board Regular
Joined
Feb 15, 2009
Messages
59
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.

I'm sure there must be an easy way to do this. Please Help!

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 土地业权人"))

Many thanks for your help.

Chuparfaan
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.

I'm sure there must be an easy way to do this. Please Help!

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 土地业权人"))

Many thanks for your help.

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:

Ganjin

Board Regular
Joined
Jan 18, 2011
Messages
179
=SUMPRODUCT(--($Y$10:$Y$58="High 高")*--(MOD(ROW($Y$10:$Y$58),2)=0),--($Z$10:$Z$58="Landlord 土地业权人"))
 

chuparfaan

Board Regular
Joined
Feb 15, 2009
Messages
59
Thanks for the reply,

Cant' seem to get those to work.

Aladin Akyurek with your formula i get VALUE!

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

Any further help would be much appreciated
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Thanks for the reply,

Cant' seem to get those to work.

Aladin Akyurek with your formula i get VALUE!

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 土地业权人"))
 

chuparfaan

Board Regular
Joined
Feb 15, 2009
Messages
59
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,372
Messages
5,595,787
Members
414,021
Latest member
whyjaydee

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