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

 

[njimack]

Do you need the formula to actually return "" if the sum is less than zero, or do you just want the cell to look empty? If it's the latter, try the following formula:
=MAX(SUMPRODUCT(--(MOD(COLUMN(P27:CV27),4)=0),(P27:CV27)),0)

Format the cell as "0;-0;"

 

[Jonmo1]

What is in the intermediate cells (Q27 R27 S27, U27 V27 W27, etc..) ?

If they are NON numeric values in those cells, then you can simply sum all of them
=SUM(P27:CV27)

And
=IF(SUM(P27:CV27)>0,SUM(P27:CV27),"")

 

[JohnfromOH]

Jonmo1:
The intermediate cells all have numbers in them, so I can't use the formula you stated. I wish I could, it would be so much easier. Thank you.

 

[JohnfromOH]

Neil:

I used your formula, and I got the same number, but there is a small problem. I am using this formula to sum about 30 different products. Some products are available in some warehouses and other products in other warehouses. I need the cell to appear blank, for products that aren't carried in those warehouses. The formula that you recommended works for the products that are in stock in that warehouse. The other cells showed a - in the cell. I would prefer that it doesn't show like that. Can that be fixed while using your formula?


1. I think part of the problem is that I don't understand the formatting you suggested. Can you explain that formatting notation for me? And how I can change the formatting on my computer?

2. I am having another problem that the "" is causing. I am trying to count cells that are >0. I want the "" cell to not be counted, but excel thinks it is >0 and counts it as in stock. How can I fix that problem? It is being counted in a dichotomous manner, >0=1, 0=0.

 

[njimack]

The formatting I suggested should solve the problems you're having.

Select the cell, then press Ctrl+1 to display the Format Cells dialogue box. Select the Custom category at the bottom of the list. Underneath the word "Type:" is an input box. Enter the following (without quotes): "0;-0;"

This will ensure that any numbers that are greater than or less than zero are displayed. Anything that equals zero will not.

 

[JohnfromOH]

Thanks Neil, that works great.

 

[JohnfromOH]

Neil:

I found a problem after putting this into an adjacent cell that is doing something similar.

I have two cells that are next to each other for several warehouses. One adds the current stock. The other adds the monthly distribution. I then want to find an aggregated availability by dividing stock by distribution. The formula you gave me returns a #div error when I have 0 for both the numerator and denominator. Any thoughts.

There are actually several steps to calculating any useful info from this. I am then looking to find a percent of the products that should be available that are available. I'll post something more concrete when I can figure out how to post the excel sheet on the forum.

Any help would be great,

cheers

 

[tusharm]

Dividing by zero will always yield an error.

What do you want it to return?

Neil:

I found a problem after putting this into an adjacent cell that is doing something similar.

I have two cells that are next to each other for several warehouses. One adds the current stock. The other adds the monthly distribution. I then want to find an aggregated availability by dividing stock by distribution. The formula you gave me returns a #div error when I have 0 for both the numerator and denominator. Any thoughts.

There are actually several steps to calculating any useful info from this. I am then looking to find a percent of the products that should be available that are available. I'll post something more concrete when I can figure out how to post the excel sheet on the forum.

Any help would be great,

cheers

 

[tusharm]

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

Thanks Neil, that works great.