Forum Discussion

TaylorTaylor1's avatar
TaylorTaylor1
Qrew Trainee
9 years ago

Need to create a countif formula to count repeated values in table. Is this possible/how?

Need to create a countif formula to count repeated values in table. Is this possible/how in Quick Base? Been messing with "Summary field options", but can't quite get what I'm after. Could do it in excel and import back in, but want value to update automatically as I add more records to my table.

Data import and export
Forms
Formulas and functions

13 Replies

Sort By
Show More
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    I loaded the Kaagle Titanic Dataset which is used for studying Machine Learning Algorithms. This may appear to be unrelated to your CountIf problem but what I will show you will vastly enlarge your universe. Prepare to be blown away as I show you how to use script to produce all manner of summary reports, distinct counts, treemaps etc using this QuickBase data.

    Kaaggle Titanic Problem
    https://www.kaggle.com/c/titanic

    Triangulation 57: Jeremy Howard founder of Kaagle
    https://www.youtube.com/watch?v=hxB-rEQvBeM

    HighCharts Treemap
    http://www.highcharts.com/demo/treemap-large-dataset
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    D> Post sample data and desired output report. 
    T> Don't want a report ...

    By output report I meant output in the most general sense. Here is my view of your question: You are asking for a countif feature similar to what is used in Excel. The bottom line is that there is no analogous native functionality in QuickBase. The countif function in Excel takes a (1) range of cells in an Excel worksheet and (2) a criteria and counts up the number of cells in the range that match the criteria and displays this count in some other cell in the worksheet:
    COUNTIF( range, criteria )
    There is no concept of database tables in Excel and the only native counting you can do in QuickBase is through (1) a summary field defined for a parent child relationship or (2) through a summary report of a single table. So if the counting you want to do does not fit this mold you are out of luck using native QuickBase. You have to take an entirely different approach to find a solution and that solution involves using (1) script, (2) the QuickBase API, and (3) some JavaScript libraries that QuickBase already uses within its pages (notably jQuery and Underscore).
    The first step is to obtain the raw data using the API. Unfortunately QuickBase's API returns XML so a short utility function will be used to return JSON from API calls. Next, the JSON representing the raw data is processed is some manner and typically involves counting, indexing, sorting, grouping or performing some arithmetic and concatenation of raw data. This second step is done with script and frequently uses methods in the underscore library. The third step is to apply a template to the processed data to generate some type of HTML output which might be a simple table or results or a complex svg graphic of some sort (for instance a HighChart chart).
    Kaaggle Titanic Tasks (click on Summarize By Sex and Embarkation Button)https://haversineconsulting.quickbase.com/db/bkjtq43us?a=td

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=470

    template.html (Underscore Template) https://haversineconsulting.quickbase.com/db/bkjtbpfei?a=dbpage&pagename=template.html
    BySexAndEmbarkation.js https://haversineconsulting.quickbase.com/db/bkjtbpfei?a=dbpage&pagename=BySexAndEmbarkation.js
    Notes

     (1) more to come when I have time ...