Jump to content
  • 0

Count number of occurances in a table line


PeterER8
 Share

Question

 

Hi

Apologies upfront if my questions have been asked before (I could not find the answer). In my defense I am new to Caspio, started testing it last week and now have lots of questions. This one is regarding DataType formula in tables and specifically trying to count the number of occurrences of a specific text string in that data row. Easy thing to do in excel (yes, I am damaged by too much excel usage). Basically I am after “count cells that contain x”. I understand the aspect that this only works for the row in which the formula field is present and that is perfectly fine.

Example: I have the following table with the Counted_Field_Green field being a formula field and the values in each field below that.

Table Fields: Date, Name, Reason_1, Reason_2, Reason_3, Reason_4, Test, Counted_Field_Green

Table contents: 01/05/2021, Peter Max, Green, Yellow, Green, White, Yes, 2

 

This is what happens here, the formula in Counted_Field_green takes a look at the at the 4 Reason fields and counts the number of times that the entry “Green” shows up as a value. Kind of how countif works in excel or at least, I am after same functionality.

Is there a way to make this happen in a Caspio table?

If not is there a way to make it happen and populate a predefined field in a DataPage that calculates the presence of x in the entry fields?  

 

I really appreciate any help you can give me!!!

 

 

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hi @PeterER8,

Do you want to count only the number of cells that has 'Green' value per record? Is your expected output similar to the following?
image.png

If yes, you can just use CASE-WHEN statements in your formula field that will check if the field has 'Green' value.

(CASE WHEN [@field:Reason_1] = 'Green' THEN 1
ELSE 0
END)
+
(CASE WHEN [@field:Reason_2] = 'Green' THEN 1
ELSE 0
END)
+
(CASE WHEN [@field:Reason_3] = 'Green' THEN 1
ELSE 0
END)
+
(CASE WHEN [@field:Reason_4] = 'Green' THEN 1
ELSE 0
END)

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...