# Count unless text is in a list

#### Steven975

##### New Member
In this scenario I am trying to track how many calls a staff member made in Jan. The catch is there are some calls that are not eligable for tracking. The ineligable call statuses are in a list. Is there a way to do this?

I'm not great with excel, any help is appreciated!

Count if the 'month' is "Jan", and the 'Type' is "call", unless the 'status' is in the list below.
In the chart below, Jan should be 2, Feb 0, Mar 1
 Month Type Status Jan Call Left message Jan Call Do not call Jan Call Contacted Feb Other Mar Call Left message Mar Call Client came into branch

<tbody>
</tbody>

IneligableCalls
Do not call
Client came into branch

#### jasonb75

##### Well-known Member
Welcome to Mr Excel

Looking at your example, here are 2 ways of doing what you need. I've assumed that the data is in columns A:C, for testing I had the list of exclusions in G2:G4

=SUMPRODUCT((\$A\$2:\$A\$7="Jan")*(\$B\$2:\$B\$7="Call")*ISNA(MATCH(\$C\$2:\$C\$7,\$G\$2:\$G\$4,0)))

Or, an array formula confirmed by pressing Ctrl Shift Enter.

=COUNTIFS(\$A\$2:\$A\$7,"Jan",\$B\$2:\$B\$7,"Call")-SUM(COUNTIFS(\$A\$2:\$A\$7,"Jan",\$B\$2:\$B\$7,"Call",\$C\$2:\$C\$7,TRANSPOSE(\$G\$2:\$G\$4)))

I believe that the second formula should be quicker to recalculate if you have a lot of data in your real sheet, but have not tested that theory.

#### Eric W

##### MrExcel MVP
Welcome to the Board.

Try:

#### Steven975

##### New Member
Thanks Friend, you're the best!