Home

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

William Peaster
New Contributor

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

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

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

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

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

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.

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

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

Related Conversations
Copy/paste no longer working in Excel
Jon Firooz in Excel on
65 Replies
Security Warning: Automatic update of links has been disabled.
Mark Adams in Excel on
6 Replies
EXCEL DO NOT SHOW GRAPH MAP CHART
Mark 777 in Excel on
47 Replies
Copy and paste checkable text
tube.designing in Excel on
18 Replies
How to display many results with using VLOOKUP
Ben Wilson in Excel on
4 Replies