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
  • 544
  • 2
  • 5
  • 25
confused
  • 1
  • 1
  • 1
  • 1
    You've explained it all right, but it would be easier to actually see the workbook or at least a screenshot. Use a file sharing service and post a link. – teylyn Jun 03 '15 at 09:46
  • Or type in representations of your sheet, as was done [here](http://superuser.com/q/889201/150988) and [here](http://superuser.com/q/892744/150988).  Either way, include the data that you have now and what results you want to get. – Scott - Слава Україні Jun 03 '15 at 14:52

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,266
  • 6
  • 25
  • 39