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
Post a Comment