Under "score 1" I want it to tally up the cells under "bettor" that have values >0 where the corresponding cells under "fight" have the number 1 and the one under "odds" are also >0. Any ideas? Thanks

juntjoo =SUMPRODUCT((ISNUMBER(SEARCH("1",A4:A7)))*(B4:B7>0)*(C4:C7>0)) I suggest above formula.

Forum Discussion

Copper Contributor

Nov 16, 2021

Solved

How to write the formula for this cell?

Under "score 1" I want it to tally up the cells under "bettor" that have values >0 where the corresponding cells under "fight" have the number 1 and the one under "odds" are also >0. Any ideas? Thanks

betting score.xlsx11 KB

Excel on mobile

OliverScheurich
Nov 16, 2021
juntjoo
=SUMPRODUCT((ISNUMBER(SEARCH("1",A4:A7)))*(B4:B7>0)*(C4:C7>0))

I suggest above formula.
betting score.xlsx11 KB

4 Replies

OliverScheurich
Gold Contributor
Nov 16, 2021
juntjoo
=SUMPRODUCT((ISNUMBER(SEARCH("1",A4:A7)))*(B4:B7>0)*(C4:C7>0))

I suggest above formula.
betting score.xlsx11 KB
- juntjoo
  Copper Contributor
  Nov 16, 2021
  Oh never mind. Dumb question. Same method as you use for single cell references. Got it
- juntjoo
  Copper Contributor
  Nov 16, 2021
  Is there a way to make the "fight" and "odds" columns in the formula absolute so when I paste the formula under new bettors they remain? Thanks
- juntjoo
  Copper Contributor
  Nov 16, 2021
  Thanks so much!