Formula syntax

Tagged: data table generator, data tables, formula syntax, formulas, separator, supsystic

This topic has 8 replies, 4 voices, and was last updated 4 years, 9 months ago by Alex_support.

Viewing 9 posts - 1 through 9 (of 9 total)

Author
Posts
June 26, 2017 at 22:08 #14668

kmorrissey
Participant
Please tell me how to use these conditional formulas: COUNTIF, AVERAGEIF and SUMIF. I keep getting #ERROR.
For example:
I have a column that contains numbers, blanks, and the characters “TBD”
I would like to be able to count the number of cells containing “TBD” but cannot get this to work.
I have looked at the documentation but have not found any explanation of the syntax for these formulas.
I have tried various definitions for the “if” but cannot get it to work. Please tell me how to use this function.
These have not worked:
COUNTIF(D2:D30,”TBD”)
COUNTIF(D2:D30,”=”TBD””)
COUNTIF(D2:D30,”=’TBD'”)
COUNTIF(D2:D30,”=A39″) (where A39 contains the value TBD)
COUNTIF(D2:D30,A39)
I have also tried using numeric values and I can’t get that to work either.
COUNTIF(D2:D30,”>2″)
Thank you.
June 27, 2017 at 20:05 #14712

mila_support
Member
Hi @kmorrissey
We will try to fix these formulas in one of the next versions of the plugin.
I will inform you about the update.
July 21, 2017 at 14:48 #14965

mila_support
Member
Hello @kmorrissey
We released a new version of plugin with fixes. Please, update both your versions.
Also it will be better to use ; as delimiter in the formulas.
July 21, 2017 at 19:12 #14976

kmorrissey
Participant
Thank you so much!
How do I know when to use comma vs. semi-colon?
This page shows using semi-colons:
https://supsystic.com/documentation/use-formulas-tables/
This page shows commas:
https://supsystic.com/documentation/list-formulas-supported-data-tables/
Thank you
July 25, 2017 at 18:21 #14995

mila_support
Member
Hi @kmorrissey
Thank you for reporting us about the issue.
In order to make your formula work correctly, you can use comma for simple formulas. But there is no guarantee it will make formula work right.
We recommend to use semi-colon as a separator inside formulas. The same you can meet at Google Sheets.
We will add this recommendation inside our documentation of Data Tables Plugin any time soon.
June 7, 2018 at 21:17 #18074

bjorn106086
Participant
Hi mila_support,
what is the correct syntax? If have the same issue as above and everything is on newest version.
Regards,
Toni
P.S. Do these formulas also work on dropdown fields?
June 8, 2018 at 13:30 #18081

Alex_support
Member
Hello, @bjorn106086
Did you tried this? – “We recommend to use semi-colon as a separator inside formulas. The same you can meet at Google Sheets.”
Also, can you give us a link to your table and example of formulas, which are not working for you?
June 8, 2018 at 14:31 #18087

bjorn106086
Participant
Hi Alex_support,
I found out how to solve the problem. Strangely enough, Comma does work and Semicolon not. Also I have to spell the text in all caps.
e.g. =countif(B2:B30,”YES”)
Another thing that I noticed is regarding conditional formatting. If I want to format everything where “Yes” is selected green in three columns, I can’t make it work with one rule. It doesn’t matter if I use &,;
e.G.
B2:B30;C2:C30;D2:D30
B2:B30,C2:C30,D2:D30
B2:B30&C2:C30&D2:D30
Regards,
Toni
June 8, 2018 at 16:14 #18090

Alex_support
Member
Thank you for clarifying. We’ll take a look on this issue.
Author
Posts