0

I am dealing with job applications for 8 different sites. People can apply for any combination of sites i.e 1 of them, 2 of them, all of them.

Some applicants have specifically said they don't want to apply for 1 or 2 particular sites.

I have used this formula =COUNTIF(B1:B35,"*All*") to count when someone has specified 'all' sites or used site names to count each of the others.

I also have a tally for people applying to each site but now I want to subtract the numbers from the totals where people have specified NOT a particular site.

E.g applicant 1 applies for site A, applicant 2 applies for site site A & site B, applicant 3 applies for sites A, B and C, applicant 4 applies for all sites except A.

How do I count the applicants for 'All sites' or any of the others while also minusing applicant 4 from A's totals?

Hope I've explained this ok? Sorry if not.

Steve can help
  • 554
confused
  • 1

1 Answers1

1

You need to create a schema that will support your analysis. Say we organize data like:

enter image description here

Where the total for each row is in column J and the total for each column is in row #18. Now to find how many people picked ALL we could use:

=COUNTIF(J:J,8)

and to find how many people picked ALL EXCEPT siteA we could use:

=SUMPRODUCT((J:J=7)*(B:B=0))
Gary's Student
  • 19,636