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),"")

 

[JohnfromOH]

If there is no answer, I want it to reply with a blank cell instead of #Div/

 

[JohnfromOH]

I think I need to explain this more in depthly.
1. I am creating a form to report stock levels and distribution form remote areas in developing countries. There are going to be a lot of people adding info to this sheet who have very limited knowledge of Excel.
2. All warehouses don't have the same requirements for which medicines they need to have on hand. The warehouse has to indicate with a 1 or 0 for needed meds vs. non needed.
3. The above mentioned formula is trying to add the stock in each warehouse into an aggregated column.
4. The adjacent cell has a similar formula for the distribution.
5. The next cell divides stock by distribution to find months of stock on hand.
6. Columns will likely get added and subtracted, because the users don't know how to use excel well. The need to add is important, because not all warehouses will report each month.
7. The below table is basically how I want it to look.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Medicine[/TD]
[TD]Obligatory 1=yes, 0=no
[/TD]
[TD]quantity avail
[/TD]
[TD]distribution
[/TD]
[TD]months of stock
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]5000
[/TD]
[TD]500
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]b
[/TD]
[TD]1
[/TD]
[TD]500
[/TD]
[TD]500
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]c
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula referenced at the beginning is the formula used to get the quantity available for medicine A. If I used Neil's formula also for the adjacent cell, it works fine for a and b. For c the months of stock returns #div, but I would like to return a blank. Otherwise the form is too cluttered with non pertinent info.

Finally, the months of stock need to be counted as available or not available in a dichotomous fashion to come up with some indicators. I am currently using a 0, >0 designation to determine the availability. "" causes an error, because it counts it as being available, even though it is not.

If you can help with the top part first, I can move on to the second part later in the thread.

 

[Aladin Akyurek]

If you are happy with Neil's idea, good. Just note that it is not robust in that if you insert or delete a column before P, the result will be incorrect.

A more robust, though more complex solution is
=SUMPRODUCT(N(OFFSET(P27,0,(ROW(INDIRECT("1:"&(INT(COLUMNS(P27:CV27)/4)+1)))-1)*4,1,1)))

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.

 

[JohnfromOH]

Dividing by zero will always yield an error.

That is why I used that really long if clause. If all the cells that were to be added were blank, the answer would also be blank. Therefore I avoided the error.

Btw, thanks for any help you can give me.

 

[Aladin Akyurek]

I think I need to explain this more in depthly.
1. I am creating a form to report stock levels and distribution form remote areas in developing countries. There are going to be a lot of people adding info to this sheet who have very limited knowledge of Excel.
2. All warehouses don't have the same requirements for which medicines they need to have on hand. The warehouse has to indicate with a 1 or 0 for needed meds vs. non needed.
3. The above mentioned formula is trying to add the stock in each warehouse into an aggregated column.
4. The adjacent cell has a similar formula for the distribution.
5. The next cell divides stock by distribution to find months of stock on hand.
6. Columns will likely get added and subtracted, because the users don't know how to use excel well. The need to add is important, because not all warehouses will report each month.
7. The below table is basically how I want it to look.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Medicine
[/TD]
[TD]Obligatory 1=yes, 0=no
[/TD]
[TD]quantity avail
[/TD]
[TD]distribution
[/TD]
[TD]months of stock
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]5000
[/TD]
[TD]500
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]b
[/TD]
[TD]1
[/TD]
[TD]500
[/TD]
[TD]500
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]c
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula referenced at the beginning is the formula used to get the quantity available for medicine A. If I used Neil's formula also for the adjacent cell, it works fine for a and b. For c the months of stock returns #div, but I would like to return a blank. Otherwise the form is too cluttered with non pertinent info.

Finally, the months of stock need to be counted as available or not available in a dichotomous fashion to come up with some indicators. I am currently using a 0, >0 designation to determine the availability. "" causes an error, because it counts it as being available, even though it is not.

If you can help with the top part first, I can move on to the second part later in the thread.

Care to post the formulas as you implelemented them for quantity avail and distribution? Are 1 and 0 (Obligatory) tags part of the data or part of the report?

 

[JohnfromOH]

Here are the formulas. As you can see the formula is easy to work with when you have only three columns to account for. When I originally worte the formula there were only 11 columns. Now some places have more than 30 and it is very cumbersome.

Quantity on hand: =IF((SUM(J59,N59,R59)>0),SUM(J59,N59,R59,),"")
Distribution: =IF((SUM(K59,O59,S59,)>0),SUM(K59,O59,S59,),"")
Months of availability: =IF($E59=1,IF(OR(F59="", G59=""),0,+F59/G59),IF($E59<>1,IF((OR(F59="", G59="")),"",+F59/G59),)
Maybe the solution is partially formatting. Many of the "" I have wrote into this formula are so the cell is blank when there is no real info to show.

The 1 and 0 are part of the data
Just fyi, this is how I have set up the indicator to count the number of available medicines.
'=SUMPRODUCT(($E59:$E63=1)*(H59:H63>0))

Thanks for your help

 

[Jonmo1]

do you have headers?
And do the headers in the desired columns have anything in common that can be used?

Perhaps you can use SUMIF
=SUMIF(P1:CV1,"SumThisColumn",P27:CV27)

That basically says sum all cells in P27:CV27 IF the corresponding cell from Row 1 (headers) reads "SumThisColumn"
Adjust the text string to whatever the header might be.

 

[JohnfromOH]

Hi Jon,

That seems like it will work very well. Let me try it and if I have any other questions, I will get back to you. Thanks a lot.

 

[Aladin Akyurek]

Here are the formulas. As you can see the formula is easy to work with when you have only three columns to account for. When I originally worte the formula there were only 11 columns. Now some places have more than 30 and it is very cumbersome.

Quantity on hand: =IF((SUM(J59,N59,R59)>0),SUM(J59,N59,R59,),"")
Distribution: =IF((SUM(K59,O59,S59,)>0),SUM(K59,O59,S59,),"")
Months of availability: =IF($E59=1,IF(OR(F59="", G59=""),0,+F59/G59),IF($E59<>1,IF((OR(F59="", G59="")),"",+F59/G59),)
Maybe the solution is partially formatting. Many of the "" I have wrote into this formula are so the cell is blank when there is no real info to show.

The 1 and 0 are part of the data
Just fyi, this is how I have set up the indicator to count the number of available medicines.
'=SUMPRODUCT(($E59:$E63=1)*(H59:H63>0))

Thanks for your help

1)

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

is ok, even if we are computing the same thing twice.

2)

=IF((SUM(K59,O59,S59,)>0),SUM(K59,O59,S59,),"")

as above.

3) Re-written:

=IF($E59=1,IF(OR(F59="", G59=""),0,+F59/G59),IF($E59<>1,IF((OR(F59="", G59="")),"",+F59/G59),)

becomes:

=IF(OR(F59="",G59=""),"",IF($E59=1,0,F59/G59))

4) Re-written:

=SUMPRODUCT(($E59:$E63=1)*(H59:H63>0))

becomes:

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

 

[JohnfromOH]

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.

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.

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