Support Board
Date/Time: Fri, 09 May 2025 13:55:12 +0000
[Programming Help] - Spreadsheet Count Values Greater Than Zero
View Count: 494
| [2023-05-28 19:26:08] |
| user_xyz - Posts: 479 |
|
I want to count/sum any value greater than 0 as 1 across 12 columns AB:AM. Is the only way to create 12 helper columns O:Z with =IF(AB>=1,1,0) etc. Then in column AA =SUM(O:Z) to count the number of instances? Since SC doesn't use sumif, countif or array functions I'm trying to streamline this without using a mega number of helper columns. AB AC AD AE AF.... 0 123 5 17 0 O P Q R S.... 0 1 1 1 0 AA Result would be 3 |
| [2023-05-30 02:37:56] |
| j4ytr4der_ - Posts: 946 |
|
To my knowledge yes you have to use helper columns. But, you might (depending on what you're trying to count) be able to use the Cumulative Sum of Study study, to get the number you're looking for without doing it in a spreadsheet.
|
| [2023-05-30 04:36:49] |
| user_xyz - Posts: 479 |
|
Good idea unfortunately you can only select one subgraphs which is equivalent to only one column... You would think there is a way to count column values as 0/1 without having a bazillion helper columns.
Date Time Of Last Edit: 2023-05-30 04:37:12
|
| [2023-05-30 05:00:18] |
| j4ytr4der_ - Posts: 946 |
|
You can however use multiple cumulative sum studies, bring them all into your spreadsheet, and work with the result of those. Not sure that's helpful in your exact case but, maybe worth considering.
|
| [2023-05-30 21:55:44] |
| Sawtooth - Posts: 4213 |
|
Is the only way to create 12 helper columns
Yes.If the max of 60 Formula Columns isn't enough, you could use one Spreadsheet Study study for these intermediate calculations, instead of multiple other studies. Its output is seen by the other spreadsheet studies. |
| [2023-05-30 23:32:16] |
| user_xyz - Posts: 479 |
|
Tom thanks for the reply. I was afraid you were going to say this. I'm always concerned about speed and bogging SC down with excessive calculations. So your suggestion would be to have a dedicated chart with all the studies (example 12) on a separate Spreadsheet Study on it's own Sheet with all the helper columns then reference the output in the main spreadsheet that I'm using with just the column of data needed (in example above it would only be AA and result of 3) Date Time Of Last Edit: 2023-05-30 23:32:55
|
| [2023-05-30 23:34:48] |
| j4ytr4der_ - Posts: 946 |
|
Could you just use a separate sheet within the same spreadsheet system, rather than an entire other system? You can reference sheets, within sheets so... maybe worth a try.
|
| [2023-05-30 23:52:32] |
| user_xyz - Posts: 479 |
|
Without complicating things - actually many of the columns I am referencing are actually from another chart. I'm using Study Overlay to get the data in the 'main' spreadsheet. Seems like either way having a separate sheet on the same chart or the secondary chart with all the helper columns is the cleanest solution. I'm going to test it now.
|
To post a message in this thread, you need to log in with your Sierra Chart account: