0

I have this google sheet with many formulas. The purpose of this sheet to record all Walkins for an institute on daily basis with their relevant status that are FU, DFU or Sales. Example format:

Row no: Col 1 | Col 2 | Col 3 | Col 4 | Col 5

1:Date | Name | Phone | Status | Remarks

2:01/06/15 | Client1 | 1001 | FU | will come again
3:01/06/15 | Client2 | 2002 | DFU | Not joining
4:02/06/15 | Client3 | 3003 | Sales | Enrolled
5:03/06/15 | Client1 | 1001 | Sales | repeat walkin, enrolled today

As soon as Client1 Status set to "Sales", any duplicate entry [which actually checks on Col 3 (Phone)] changes to "Status - Sales" automatically. So Row 2 now becomes:

2:01/06/15 | Client1 | 1001 | Sales | will come again, enrolled 03/06/15

There is another formula calculating the total Sales [=COUNTIF(D:D,"=Sales")] however it is also calculating the duplicate entries. As in above example the total number of Sales should be 1, but it is resulting 2. I tried all possible functions (or atleast upto what I am aware of) but just not getting there.

Any advice without the use of scripts/ macros.

Inderpartap Tiwana
  • 1
  • 1

1 Answers1

0

There are some standard formulae for counting distinct values here

If you can use the phone number to identify different clients and it's a genuine number without any other characters, the formula for adding a condition (only count rows containing "Sales" in column D) isn't too complicated:-

=arrayformula(SUM(IF(FREQUENCY(IF(D2:D5=ʺSalesʺ,C2:C5),IF(D2:D5=ʺSalesʺ,C2:C5))>0,1)))

But if you need to use the name it is more complicated - there are some examples here

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37