# Excel countif formula that will subtract from another number if cell contains certain values

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.

• 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

``````=COUNTIF(J:J,8)
``````=SUMPRODUCT((J:J=7)*(B:B=0))