sumproduct

[Novice]

The two formulas work seperately. I want to combine them. The results
returned are the % of cells with numbers between 0 and 100 on each worksheet.
I want the % of cells on both worksheet; so I can't just add the two results
together


=SUMPRODUCT(--(B2:F4>0),--(B2:F4<=100)/COUNT(B2:F4))

=SUMPRODUCT(--([Monday.xls]Monday!B2:F4>0),--([Monday.xls]Monday!B2:F4<=100)/COUNT([Monday.xls]Monday!B2:F4))
Thanks

 

[Earl Kiosterud]

Novice,

Your formula isn't exactly what you asked for. Cells with 0 will be treated as numbers that
don't fall in your range. This will reduce the percentage result. The following formula
works the same, except across the two sheets.

=(SUMPRODUCT((Sheet1!B2:F4>0)*(Sheet1!B2:F4<=100) )
+SUMPRODUCT((Sheet2!B2:F4>0)*(Sheet2!B2:F4<=100) ) )/ (COUNT(Sheet1!B2:F4) +
COUNT(Sheet2!B2:F4))

 

[Novice]

It worked. Thanks for pointing out that cells with 0 reduced the percentage.

Novice,

Your formula isn't exactly what you asked for. Cells with 0 will be treated as numbers that
don't fall in your range. This will reduce the percentage result. The following formula
works the same, except across the two sheets.

=(SUMPRODUCT((Sheet1!B2:F4>0)*(Sheet1!B2:F4<=100) )
+SUMPRODUCT((Sheet2!B2:F4>0)*(Sheet2!B2:F4<=100) ) )/ (COUNT(Sheet1!B2:F4) +
COUNT(Sheet2!B2:F4))
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

The two formulas work seperately. I want to combine them. The results
returned are the % of cells with numbers between 0 and 100 on each worksheet.
I want the % of cells on both worksheet; so I can't just add the two results
together


=SUMPRODUCT(--(B2:F4>0),--(B2:F4<=100)/COUNT(B2:F4))

=SUMPRODUCT(--([Monday.xls]Monday!B2:F4>0),--([Monday.xls]Monday!B2:F4<=100)/COUNT([Monday.xls]Monday!B2:F4))
Thanks