Excel: Sum an array inside a formula, i.e. do not print array out

Copper Contributor

Hi,

 

I have a formula that produces an array.  Once I click enter the cell says "resize to show all values". When I do this it prints the 30 value array in 30 cells.  However this spreadsheet is going to be rather large and have many cells like this.  I only  need the array for the sum of the values.  Is there any way to sum this array inside the cell that I have it in? I need to do this without VBA because it is going to be accessed by people that will not be familiar with running macros.

 

Essentially I need to sum an array without having all the values printed.

 

Thanks!!

3 Replies

Just wrap the formula in =SUM() and press control+shift+enter.

When I do that, the answer comes out as zero.  However, I know this is not true because when I resize it prints all my values and it should equal approc 1.5 mil. Any ideas what I may be doing wrong or another suggestion?

 

{=SUM(PIAdvCalcDat('1010 Monthly Data 2'!$B$2,"3/1/2017 12:12:00 am","4/1/2017 12:12:00 am","1d","maximum","time-weighted",0,1,0,"database"))}


This is what the formula looks like at the moment.

Hello,

it looks like you are using the PI add-in from Osisoft. The "resize to show all values" is something typical for their functions and you'd need to ask in Osisoft's community how to resolve that into a single value.

 

If you are comfortable with VBA, you may want to take a look here: https://pisquare.osisoft.com/thread/7162