How to count the number of times a value appears based on multiple criteria in another column?

DesireeJones

Hello,

I am trying to create a formula that counts the number of times an email appears based on a number of selections made from a drop-down menu. The process is for counting the number of trainings completed per quarter per employee. The trainings are submitted through a form and only one training title can be submitted at a time.

Here is an example:

I enter my email address, djones@acme.com, for the training title, "Onboarding", then I submit the form. I enter my email address, djones@acme.com, for the training title, "SharePoint", then I submit the form. I enter my email address, djones@acme.com, for the training title, "Conflict Resolution", then I submit the form. Now I have 3 submissions tied to the same email address for 3 separate training titles. I have one more training to do, "Outlook", but I have not completed the training, so I have not submitted a form for it.

How do I count the number of times my email address appears based only if the training titles were selected? When I submit the last training, this needs to be included in the count whenever it is submitted.

I have tried formulas with variations that include: IF, COUNTIF, COUNTIFS, CONTAINS, HAS, DISTINCT, and COLLECT.

Thank you!

Paul Newcome

If you are only submitting the form when the training is completed, then you should be able to just count the number of times the email address appears regardless of what the dropdown selection is.

=COUNTIFS([Email Address]:[Email Address], @cell = "djones@acme.com")

DesireeJones

Hi @Paul Newcome ,

I am currently trying to count the number of times an email address appears for specific training titles associated with Quarter 1. So when I just count the email address itself with no other criteria, I get the count of all of the training titles which include titles from Quarter 2 and Quarter 3. I have a list of specific training titles from the drop-down menu that is associated with each quarter.

Paul Newcome

Ah. In that case you would need a COUNTIFS.

=COUNTIFS(range 1, criteria 1, range 2, criteria 2, range 3, criteria 3)

DesireeJones

@Paul Newcome

I tried to use COUNTIFS, but it returns 0. There are submissions for the trainings, but I think the formula is not counting the trainings individually, rather it will only count if all of the criteria is met.

Here is the formula I used:

=COUNTIFS({2022 Training Records Employee Email}, Email@row, {2022 Training Records Q1 Requirement Met}, "The Training Matrix", {2022 Training Records Q1 Requirement Met}, "Controlling Conflict, Stress, and Time in a Customer Service Environment", {2022 Training Records Q1 Requirement Met}, "Q1 Interactive Training Module", {2022 Training Records Q1 Requirement Met}, "Q1 Systems Training", {2022 Training Records Q1 Requirement Met}, "Specialist Orientation", {2022 Training Records Q1 Requirement Met}, "Understanding Unconscious Bias")

The formula returns 0 because not all of the trainings were submitted just yet.