Count unless text is in a list

[Steven975]

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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Call[/TD]
[TD]Left message[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Call[/TD]
[TD]Do not call[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Call[/TD]
[TD]Contacted[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]Other[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]Call[/TD]
[TD]Left message[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]Call[/TD]
[TD]Client came into branch[/TD]
[/TR]
</tbody>[/TABLE]











IneligableCalls
Do not call
Client came into branch

 

[jasonb75]

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]

Welcome to the Board.

Try:

Book1
	A	B	C	D	E	F	G
1	Month	Type	Status		Ineligible Calls		Eligible count
2	Jan	Call	Left message		Do not call		2
3	Jan	Call	Do not call		Client came into branch
4	Jan	Call	Contacted
5	Feb	Other
6	Mar	Call	Left message
7	Mar	Call	Client came into branch
Sheet8

Cell Formulas
Range	Formula
G2	=COUNTIFS(A:A,"Jan",B:B,"call")-SUMPRODUCT(COUNTIFS(A:A,"Jan",B:B,"call",C:C,E2:E3))

 

[Steven975]

Thanks Friend, you're the best!

 

[Eric W]

Glad we could help!