Help with complex SUMPRODUCT formula

[David Lipetz]

Hi,

Getting this SUMPRODUCT forumula to work has me stumped.

For range: $E$3:$E$352 is not blank
For range: $S$3:$S$352 is equal to or greater than $P$3:$P$352 *2 (cells
where P is => 2x S)
SUM Range:$S$3:$S$352

I've come up with this formula, but clearly it does not work:
=SUMPRODUCT(--($E$3:$E$352<>0),(($S$3:$S$352>=($P$3:$P$352*2))),($S$3:$S$352))

Additionally, I need a derivation of this forumula which will count (rather
than sum) the number of cells in S which meet the first two criteria.

I tried this, but it does not work:
=SUMPRODUCT(--($E$3:$E$352<>0),--(($S$3:$S$352>=($P$3:$P$352*2))))

Appreciate any guidance that can be provided.

Thanks,
David

 

[David Lipetz]

Never mind. My formula's did work. Seems like my audit was flawed and the
formula was correct.

 

[Pete_UK]

Try this to count them:

=SUMPRODUCT(($E$3:$E$352<>0)*($S$3:$S$352>=$P$3:$P$352*2))

and this to sum them:

=SUMPRODUCT(($E$3:$E$352<>0)*($S$3:$S$352>=$P$3:$P$352*2)*($S$3:$S$35­
2))

Hope this helps.

Pete

 

[David Lipetz]

Thanks Pete! Your response helped me clean up and correct my formulas.

Really appreciate the fast response.

Thanks,
David


Try this to count them:

=SUMPRODUCT(($E$3:$E$352<>0)*($S$3:$S$352>=$P$3:$P$352*2))

and this to sum them:

=SUMPRODUCT(($E$3:$E$352<>0)*($S$3:$S$352>=$P$3:$P$352*2)*($S$3:$S$35­
2))

Hope this helps.

Pete

 

[Pete_UK]

Thanks for feeding back - I just happened to be browsing when your
post came in.

Pete