Simplify Formula

[ncrb_10]

Hello All,

I am wondering if there are any ideas to simplify/shorten the following formula. It works fine, but it is fairly long.

=IF(SUMIF('Sheet2'!$C$5:$C$200,G21,'Sheet2'!$G$5:$G$200)>0,SUMIF('Sheet2'!$C$5:$C$200,G21,'Sheet2'!$G$5:$G$200),SUMIF('Sheet2'!$C$5:$C$200,G21,'Sheet2'!$F$5:$F$200))

Thanks!

 

[Joe4]

One way to make it shorter is to enlist the use of named ranges, which will shorten your range references.

If you want to see if there is a way to use less functions in it, please explain your layout in detail, and describe exactly what it is that you are trying to do.

 

[Tetra201]

The formula can be slightly shortened by moving the IF inside a SUMIF:

=SUMIF(Sheet2!$C$5:$C$200,G21,IF(SUMIF(Sheet2!$C$5:$C$200,G21,Sheet2!$G$5:$G$200)>0,Sheet2!$G$5:$G$200,Sheet2!$F$5:$F$200))