If and Countif Function and Slicer

Question

ColumnA - Agent name (Say 1, 2, 3....)

ColumnB - Dealer Name(Say a, b, c, d, e, f, ....)

1 agent have multiple dealers, but a dealer must have one unique agent

ColumnC - Year(2016,NA)

Sample of the table:

Agent Dealer Year
1     AB     2016
1     MN     2016
1     XY     NA
2     CD     2016
2     EF     NA
2     GH     NA
3     RT     2016
3     TN     2016
3     RZ     2016

This formula helps in counting the total active agent name = total agent name minus total agent name with NA

For Agent1, active dealer count = 3-1 = 2

For Agent2, active dealer count = 3-2 = 1

For Agent3, active dealer count = 3-0 = 3

How do I get the active agent details count, agent wise.

Like For Active Agent1 = Total count of agent(1) - total year (na) for agent1

For Active Agent2 = Total count of agent(2) - total year (na) for Agent2

if and count if and counta

=if(A:A="AGENTNAME",(COUNTA('Working Sheet'!$C$4:$C$1048576)-COUNTIF('Working Sheet'!$C$4:$C$1048576,"NA")

it would be helpful to see sample data and expected output. It might be that COUNTIFS will do what you want, but I'm not sure I follow your explanation properly — cybernetic.nomad

Cohan Cohan · Accepted Answer · 2019-04-05T20:53:56

Use COUNTIFS. It allows you to look at multiple conditions at the same time. the <> operator inverts the match so it counts anything that isn't NA.

I copied your table in to columns A:C, and put the following formulas in cells F2 and G2 respectfully. (Agent is in column E)

=COUNTIFS(A:A,E2,C:C,"NA")
=COUNTIFS(A:A,E2,C:C,"<>NA")

This yielded the following

Agent   Inactive    Active
1       1           2
2       2           1
3       0           3

Also, just a matter of style, but if you don't need to specify a specific range of rows, you can tell it to look at the entire column. C:C is a lot easier to parse than $C$4:$C$1048576. Even if rows 1:3 have other information, as long as it doesn't match the agent name, you're good to go.

If and Countif Function and Slicer

1 Answers