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.

  • 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 3 '15 at 9:46
  • Or type in representations of your sheet, as was done here and here.  Either way, include the data that you have now and what results you want to get. – Scott Jun 3 '15 at 14:52

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:


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.