Please help with my function!

[wanyunyang]

Hi guys,

I have a table as shown:

Salesrep Date  Customer  Item  Checked

  QQ         1-1         001          A          1

  QQ         1-1        001          B          0

  QQ         1-2        002          A          0

  QQ          1-3        002          B          1

For each salesrep, I want to show the number of visit with nothing checked in this 3-day period with each customer. The result for QQ should be like:

Customer  Count

     001          0

     002          1

My function is like:

SUM(IF(Aggr(SUM(Checked), Date&Customer)=0,1,0))

But it shows nothing at all. Please help! Thanks in advance!

[sunny_talwar]

What is the logic behind the output?

[wanyunyang]

Customer 001 was only visited once (1-1), during this visit, A is checked, not blank visit. So blank visit count for customer 001=0.

Customer 002 was visited twice (1-2, 1-3). On 1-2, nothing is checked, one blank visit. On 1-3, B is checked, not blank visit. So blank visit count for customer 002=1.

[sasiparupudi1 · Accepted solution]

SUM(IF(Aggr(SUM(Checked), Date,Customer)=0,1,0))