Help with Calculation

Carlos Yanes

Hey all,

I have a form with multiple fields. I'm trying to figure out how I can create some type of count that would tell me, how many fields in that form were filled out vs. not filled out. In addition, I'd need to apply several filters to the data that is being counted.

Is this even possible? 🤯😬

Thanks,

Carlos

Paul Newcome

You are going to have to use a series of IF statements outputting a one and adding them together to get the count.

=IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + ....................................

Once you have your count, you would want to compare it to the total number required

=IF(IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + .................................... = 11, "output if all are filled out", "output if any are missing")

Then if you only wanted that to happen based on criteria in another column:

=IF([Another Column]@row = "criteria", IF(IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + .................................... = 11, "output if all are filled out", "output if any are missing"))

Paul Newcome

You can insert an additional column and use a COUNTIFS formula such as

=COUNTIFS([First Form Column]@row:[Last Form Column]@row, @cell <> "")

This will count how many fields are not blank.

Carlos Yanes

@Paul Newcome - Thanks for the suggestion. How would I use the above if there is a mix of required and non required (and I only want to count the required)? Can the same logic be used to do the counts by column and but also adding a "filter" for a different column?

Paul Newcome

How many are required, and are they grouped together or do they have "unrequired" columns in between?

What exactly do you mean by adding a "filter" for a different column?

Carlos Yanes

@Paul Newcome I have 11 that are required that are mixed in between the non-required ones. Regarding the filter, then I have two ensure that the counts only happen on cells that meet the a certain criteria based on data in another column. What I'm trying to do is convoluted. 😱

Paul Newcome

You are going to have to use a series of IF statements outputting a one and adding them together to get the count.

=IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + ....................................

Once you have your count, you would want to compare it to the total number required

=IF(IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + .................................... = 11, "output if all are filled out", "output if any are missing")

Then if you only wanted that to happen based on criteria in another column:

=IF([Another Column]@row = "criteria", IF(IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + .................................... = 11, "output if all are filled out", "output if any are missing"))

Carlos Yanes

Thank you!!

Paul Newcome

Happy to help. 👍️