Sumproduct Formula

[chuparfaan]

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

 

[Aladin Akyurek]

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

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

 

[Ganjin]

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

 

[chuparfaan]

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]

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:

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

 

[chuparfaan]

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]

Many thanks Guys - it works now.

Great.

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

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