Overly cumbersome Excel formula

[JohnfromOH]

Is it possible to simplify this formula?

=IF((SUM(P27,T27,X27,AB27,AF27,AJ27,AN27,AR27,AV27,AZ27,BD27,BH27,BL27,BP27,BT27,BX27,CB27,CF27,CJ27,CN27,CR27,CV27)>0),SUM(P27,T27,X27,AB27,AF27,AJ27,AN27,AR27,AV27,AZ27,BD27,BH27,BL27,BP27,BT27,BX27,CB27,CF27,CJ27,CN27,CR27,CV27),"")

 

[tusharm]

If I were in your shoes, the only thing I'd do is use a defined name to 'hide' (and in the process improve maintainability of) the source cells.

Currently, your approach requires 2 points of contact if you want to modify a formula. From my cursory review of the discussion, every suggestion you've received requires at least 2 points of contact to modify a formula.

Here's how you keep the formulas understandable to less advanced consumers and simplify maintenance.

Create a named range reference (Formulas tab | Name Manager button | follow directions) to get
CellsToSum1 =Sheet1!$O$27,Sheet1!$S$27,Sheet1!$W$27,Sheet1!$AA$27,Sheet1!$AE$27,Sheet1!$AI$27,Sheet1!$AM$27,Sheet1!$AQ$27,Sheet1!$AU$27,Sheet1!$AY$27,Sheet1!$BC$27,Sheet1!$BG$27,Sheet1!$BK$27,Sheet1!$BO$27,Sheet1!$BS$27,Sheet1!$BW$27,Sheet1!$CA$27,Sheet1!$CE$27,Sheet1!$CI$27,Sheet1!$CM$27,Sheet1!$CQ$27,Sheet1!$CU$27

Similarly, create CellsToSum2 with the other cells of interest.

Now, your formulas become

Quantity on hand: =IF((SUM(CellsToSum1)>0),SUM(CellsToSum1),"")
Distribution: =IF((SUM(CellsToSum2)>0),SUM(CellsToSum2),"")
Months of availability: =IF($E59=1,IF(OR(F59="", G59=""),0,F59/G59),IF(OR(F59="", G59=""),"",F59/G59))

Easy to read and maintenance is trivial. To add another cell to CellsToSum1 modify the named range reference and you are done.

Thanks for your help Aladin,

The problem with this


is it becomes this with a lot of columns to add.


It is confusing for people besides me to add more columns and make the formula work.

Let me try the other formulas you have above. Does the isnumber part of the formula you gave me eliminate the problem with ""? what do the -- do in the formula? Its not something I have ever used.

 

[Aladin Akyurek]

Thanks for your help Aladin,

The problem with this

=IF((SUM(J59,N59,R59)>0),SUM(J59,N59,R59,),"")

is it becomes this with a lot of columns to add.

=IF((SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27)>0),SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27),"")

It is confusing for people besides me to add more columns and make the formula work.

Try instead:

=SUMPRODUCT(--(MOD(COLUMN(J59:R59)-ROW(J59),4)=0),J59:R59)

=SUMPRODUCT(--(MOD(COLUMN(O27:CU27)-ROW(O27),4)=0),O27:CU27)

Custom format the formula cells (cells housing these formulas) as:

[=0]"";General

The ranges in question J59:R59 and O27:CU27 can be also assigned names either dynamic or static and these used in formulas, reducing the formula maintenance load.

Let me try the other formulas you have above. Does the isnumber part of the formula you gave me eliminate the problem with ""?

Yes

what do the -- do in the formula? Its not something I have ever used.

=SUMPRODUCT(--($E59:$E63=1),--ISNUMBER(H59:H63),--(H59:H63>0))

SumProduct needs numbers to work. The -- bit converts the TRUE/FALSE values that result from an evaluation such as ISNUMBER(H59:H63) into the 1/0 values and so satisfies SumProduct's need.

 

[JohnfromOH]

Thanks Aladin and Tusharm

 

[JohnfromOH]

When I use this

=SUMPRODUCT(--(MOD(COLUMN(O27:CU27)-ROW(O27),4)=0),O27:CU27)

for this

=IF((SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27,)>0),SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27),"")

both answers are the same.

When I copy the formula to the adjacent cell, I get the wrong answer.

=IF((SUM(P47,T47,X47,AB47,AF47,AJ47,AN47,AR47,AV47,AZ47,BD47,BH47,BL47,BP47,BT47,BX47,CB47,CF47,CJ47,CN47,CR47,CV47,)>0),SUM(P47,T47,X47,AB47,AF47,AJ47,AN47,AR47,AV47,AZ47,BD47,BH47,BL47,BP47,BT47,BX47,CB47,CF47,CJ47,CN47,CR47,CV47),"")

= 47870

=SUMPRODUCT(--(MOD(COLUMN(P27:CV27)-ROW(P27),4)=0),P27:CV27)

= 471680

Can anyone help?

Thanks in advance.

 

[Siyanna]

Hi Aladin,

is there any chance you can explain how this number format works
[=0]"";General i.e the brackets"" and general

thank you

 

[tusharm]

It was sheer luck that COLUMN(...) - ROW(...) worked. Use COLUMN(...) - COLUMN(...)

When I use this

=SUMPRODUCT(--(MOD(COLUMN(O27:CU27)-ROW(O27),4)=0),O27:CU27)

for this

=IF((SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27,)>0),SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27),"")

both answers are the same.

When I copy the formula to the adjacent cell, I get the wrong answer.

=IF((SUM(P47,T47,X47,AB47,AF47,AJ47,AN47,AR47,AV47,AZ47,BD47,BH47,BL47,BP47,BT47,BX47,CB47,CF47,CJ47,CN47,CR47,CV47,)>0),SUM(P47,T47,X47,AB47,AF47,AJ47,AN47,AR47,AV47,AZ47,BD47,BH47,BL47,BP47,BT47,BX47,CB47,CF47,CJ47,CN47,CR47,CV47),"")

= 47870

=SUMPRODUCT(--(MOD(COLUMN(P27:CV27)-ROW(P27),4)=0),P27:CV27)

= 471680

Can anyone help?

Thanks in advance.

 

[Aladin Akyurek]

Modifying the SumProduct bit in the suggestion from...

=MAX(SUMPRODUCT(--(MOD(COLUMN(P27:CV27),4)=0),(P27:CV27)),0)

to

=MAX(SUMPRODUCT(--(MOD(COLUMN(P27:CV27)-COLUMN(P27),4)=0),(P27:CV27)),0)

would provide that robustness.

When I use this

=SUMPRODUCT(--(MOD(COLUMN(O27:CU27)-ROW(O27),4)=0),O27:CU27)

for this

=IF((SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27,)>0),SUM(O27,S27,W27,AA27,AE27,AI27,AM27,AQ27,AU27,AY27,BC27,BG27,BK27,BO27,BS27,BW27,CA27,CE27,CI27,CM27,CQ27,CU27),"")

both answers are the same.

When I copy the formula to the adjacent cell, I get the wrong answer.

=IF((SUM(P47,T47,X47,AB47,AF47,AJ47,AN47,AR47,AV47,AZ47,BD47,BH47,BL47,BP47,BT47,BX47,CB47,CF47,CJ47,CN47,CR47,CV47,)>0),SUM(P47,T47,X47,AB47,AF47,AJ47,AN47,AR47,AV47,AZ47,BD47,BH47,BL47,BP47,BT47,BX47,CB47,CF47,CJ47,CN47,CR47,CV47),"")

= 47870

=SUMPRODUCT(--(MOD(COLUMN(P27:CV27)-ROW(P27),4)=0),P27:CV27)

= 471680

Can anyone help?

Thanks in advance.

My bad. Since we have to do with horizontal ranges as Tushar also notes, replace ROW with COLUMN.

 

[Aladin Akyurek]

Hi Aladin,

is there any chance you can explain how this number format works
[=0]"";General i.e the brackets"" and general

thank you

This is purely for display purposes.

[=0]"";General means if the cell value is 0, format that cell to diplay a blank, otherwise display it as is.

 

[JohnfromOH]

Can this countif statement be simplified? Thanks to anyone who can help me.

=IF((COUNTIF($O27,0)+COUNTIF($S27,0)+COUNTIF($W27,0)+COUNTIF($AA27,0)+...+COUNTIF($FK27,0)+COUNTIF($FO27,0))>0,(COUNTIF($O27,0)+COUNTIF($S27,0)+COUNTIF($W27,0)+COUNTIF($AA27,0)+...+COUNTIF($FK27,0)+COUNTIF($FO27,0)),"")

 

[shg]

One way:

=IFERROR(1 / (1 / (COUNTIF($O27,0) + COUNTIF($S27,0) + COUNTIF($W27,0) + COUNTIF($AA27,0) + COUNTIF($FK27,0) + COUNTIF($FO27,0))), "")