Sumif on multiple ranges

[baindes]

Hello,

I have this formula which works fine: IF(F90>0,F90/SUMIF($F$90:$F$96,">0"),"-") However I am now trying to use the same formula but in a non-contiguous range. I want: IF(F90>0,F90/SUMIF($F$50,$F11,$F$190,$F$200,">0"),"-"). This formula will not work because it is not a range. I also tried sumproduct but maybe I am doing something wrong.

Thank you for any suggestions.

 

[barry houdini]

Not recommended for large numbers of cells but for 4......

=IF(F90>0,F90/(($F$50>0)+($F11>0)+($F$190>0)+($F$200>0)),"-")

 

[baindes]

Yes, that is a problem as I will have a range with over 15 cells. Is there another way?

Thank you~

 

[barry houdini]

Perhaps a little simpler, then.....

=IF(F90>0,F90/SUM(SUMIF(INDIRECT({"F50","F11","F190","F200"}),">0")),"-")

.....edit: and that original version wouldn't do quite as you wanted anyway - it counts the >0 values rather than summing them, this one will sum as required...thanks to Marcelo Branco for pointing that out.

Note: I assume you have some negative values in those cells, if there are just zeroes and positives you can just sum them......

 

[baindes]

That is excellent! Thank you!

 

[Marcelo Branco]

Perhaps a little simpler, then.....

=IF(F90>0,F90/SUM(SUMIF(INDIRECT({"F50","F11","F190","F200"}),">0")),"-")

.....edit: and that original version wouldn't do quite as you wanted anyway - it counts the >0 values rather than summing them, this one will sum as required...thanks to Marcelo Branco for pointing that out.

Note: I assume you have some negative values in those cells, if there are just zeroes and positives you can just sum them......

My pleasure.

M.