Creating calculations in rows for dynamic/unknown columns

[michalikm]

Hello all!

I'm attempting to solve a problem within an app I'm creating, but I've run out of ideas. I need to add rows to the bottom of a spreadsheet, that essentially perform a COUNTIF function in excel. I know I can transpose and cross tab to show a count, but I want to only be able to count based on defined criteria (ie. 3 added rows showing values above 50%, 20%-49%, and sub 20%). I don't believe I can use the summarize tool, since the app will be handling a dynamic number of unknown columns. Any thoughts on how I could accomplish this?

[ChrisK1]

@michalikm  Do you happen to have a sample file to post so we can see the type of data you're working? There are several formulas that will help your identify matches based on your criteria. You can use the formula tool to create a field to flag whether or not it counts into your 3 categories. If the values are spaced out in multiple columns, you can transpose them into 2 columns and perform your COUNTIF formula on 1 column versus several unknown variable number of columns. All of this is just speculation but might be more clear when/if you're able to provide examples.

[joshbennett]

Hi @michalikm, 

This thread may be helpful to look at: Equivalent to Excel COUNTIF() function

Let me know if this helps or if you are still stuck.

[michalikm]

I'm essentially trying to create and append the bottom portion of this sample (shown below/attached spreadsheet). It's 3 COUNTIF's, 1 COUNT, a simple equation, and a ranking (I figured if I could learn how to append the COUNTIF portion, I could use that to figure out the rest)... but my column headers and the number of columns won't always be consistent, so I need it to be dynamic. I know for the COUNT portion, i can just transpose and cross tan, but i couldn’t get that to work for the COUNTIF’s. I don’t think i can use the summary tool, since my columns will need to be dynamic. I hope this makes sense, but if not I can try to elaborate more.

[michalikm · Accepted solution]

 Ok, I apologize. Rookie mistake... I didn't realize I could scroll down on the list of options within the cross tab tool to create a total row or column. That was the piece I was missing. Thank you all!