excel formula - google sheet count unique values multiple criteria -


i have google sheet many formulas. purpose of sheet record walkins institute on daily basis relevant status 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 | 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 client1 status set "sales", duplicate entry [which checks on col 3 (phone)] changes "status - sales" automatically. row 2 becomes:

2:01/06/15 | client1 | 1001 | sales | come again, enrolled 03/06/15 

there formula calculating total sales [=countif(d:d,"=sales")] calculating duplicate entries. in above example total number of sales should 1, resulting 2. tried possible functions (or atleast upto aware of) not getting there.

any advice without use of scripts/ macros.

there standard formulae counting distinct values here

if can use phone number identify different clients , it's genuine number without other characters, formula adding condition (only count rows containing "sales" in column d) isn't complicated:-

=arrayformula(sum(if(frequency(if(d2:d5=ʺsalesʺ,c2:c5),if(d2:d5=ʺsalesʺ,c2:c5))>0,1))) 

but if need use name more complicated - there examples here


Comments

Popular posts from this blog

How has firefox/gecko HTML+CSS rendering changed in version 38? -

javascript - Complex json ng-repeat -

jquery - Cloning of rows and columns from the old table into the new with colSpan and rowSpan -