Calculating a % of fields in a prefilled table means that you're solving for the number of instances a criteria is met divided the total number of rows in the table. This formula works for both a prefilled and a default table.
In order to consider it as a count, the row must meet Criteria_1, Criteria_2,... Criteria_n.
In your summary table (prefilled data table), use the formula to your display cell in the display table:
=COUNTIFS(Source_Table!Column:Column, Criteria_1, Source_Table!Column:Column, Criteria 2, Source_Table!Column:Column, Criteria_n)/Number*100
Note: This formula is used for when you know the total number of rows in the prefilled table.
Note: You cross reference the formula to your Source_Table.
where:
Source_Table - is your prefilled data table
Column - the column which you want to solve the number of instances
Criteria_1, Criteria_2 - the conditions which make the row true for it to count
Number - the total number of rows in your prefilled data table
or
=COUNTIFS(Source_Table!Column:Column, Criteria_1, Source_Table!Column:Column, Criteria 2, Source_Table!Column:Column, Criteria_n)/COUNTA(Source_Table!Column:Column)*100
Note: The COUNTIFS() formula is used for when you have multiple criteria in order to consider it has a count.
Note: This formula is used for when you don't know the total number of rows in the prefilled table such as (1) there is no auto-numbering of the rows; (2) it's not necessary to number them and; (3) there's too many items.
Note: You cross reference the formula to your Source_Table.
where:
Source_Table - is your prefilled data table
Column - the column which you want to solve the number of instances
Criteria_1, Criteria_2 - the conditions which make the row true for it to count
Number - the total number of rows in your prefilled data table
For example:
Suppose that we have a 2-day safety training program for 10 employees with one test per day. The company wants to know the % passing rate for the first test, second test, and overall % passing for those who passed both.
Table 1 (Source table Prefilled Table)
Employee (Text Field) | 1st Day Post-Exam Training (Pass/Fail dropdown list) - B1 | 2nd day Post-Exam Training (Pass/Fail dropdown list) - C1 |
Employee 1 | Pass | Pass |
Employee 2 | Pass | Pass |
Employee 3 | Fail | Pass |
Employee 4 | Fail | Pass |
Employee 5 | Pass | Pass |
Employee 6 | Fail | Fail |
Employee 7 | Pass | Pass |
Employee 8 | Pass | Pass |
Employee 9 | Pass | Pass |
Employee 10 | Pass | Pass |
Note: The Pass/Fail texts are in a drop down list
Table 2 (Display table Prefilled Table)
1st day post-exam passing rate | (Formula field) - B1 |
2nd day post-exam passing rate | (Formula field) - B2 |
Overall passing rate | (Formula field) - B3 |
1st day post-exam passing rate, place formula in Table 2 cell B1:
=COUNTIF(Table1!B:B, "Pass")/10*100
2nd day post-exam passing rate, place formula in Table 2 cell B2:
=COUNTIF(Table1!C:C, "Pass")/10*100
Overall passing rate
=COUNTIFS(Table1!B:B, "Pass", Table1!C:C, "Pass")/10*100