Forum Discussion
new people only from array
Hi Everyone
Suppose you have the list below: names and status. Status may change from upcoming to closed with time meaning status is dynamic.
I am looking for a function to return the count of new names only which have upcoming. So for example George, Joseph and Anthony are shown in closed and upcoming so they will not be counted. But Arthur, Bill and Peter are new folks without previously seen in closed. So the formula should return simply 3. Note that Arthur is repeated twice in upcoming but he must be counted as once.
As variant
=LET( names, A2:A13, status, B2:B13, upcoming, FILTER(names, status="Upcoming"), closed, FILTER(names, status="Closed"), SUM( ISNA( XMATCH( upcoming, closed ) )*1 ) )
As variant
=LET( names, A2:A13, status, B2:B13, upcoming, FILTER(names, status="Upcoming"), closed, FILTER(names, status="Closed"), SUM( ISNA( XMATCH( upcoming, closed ) )*1 ) )SergeiBaklanI think you missed UNIQUE() function as OP wants to count each person only one time. So, formula would may be-
=LET( names, A2:A13, status, B2:B13, upcoming, UNIQUE(FILTER(names, status="Upcoming")), closed, UNIQUE(FILTER(names, status="Closed")), SUM( ISNA( XMATCH( upcoming, closed ) )*1 ) )Harun24HR , yes, thank you for the correction
worked like a charm, thank you so much SergeiBaklan
sameryamak , you are welcome
Unique count should be 2 as Peter is in Closed list (as per your screenshot). Try the following formula-
=COUNTA(UNIQUE(FILTER(A2:A13,COUNTIFS(A:A,A2:A13,B:B,"Closed")=0)))thank you so much. Frankly got different results between ur formula and Sergei formula when I tried to apply it to my raw data and honestly I dont know the correct result because it's a 200 row data formula
would you mind please explain A:A and B:B?hi both
I think I am confused now. I tried both formula I am getting different results
=LET(
names, Data!$C$2:$C$172,
status, Data!$I$2:$I$172,
upcoming, UNIQUE(FILTER(names, status="Upcoming")),
closed, UNIQUE(FILTER(names, status="Closed")), SUM( ISNA( XMATCH( upcoming, closed ) )*1 )
)leads to 30
while
=COUNTA(UNIQUE(FILTER(Data!$C$2:$C$172,COUNTIFS(Data!$C$2:$C$172,Data!$C$2:$C$172,Data!$I$2:$I$172,"Closed")=0)))
leads to 31
can you please help me understand this difference? Note: I can not visually tell because the data is a lot
First apply both formula to your sample data as shown on screenshot. Also, you may test on my Excel sheet that I have attached to my answer. Then observe result you got and compare which formula gibe you correct result. Then apply the correct formula to your actual dataset.
Just an excuse to use SUMPRODUCT. I thought it was heading to obsolescence!
= LET( distinctnames, UNIQUE(Names), upcoming, SIGN(COUNTIFS(Names, distinctnames, Status, "Upcoming")), open, 1 - SIGN(COUNTIFS(Names, distinctnames, Status, "Closed")), SUMPRODUCT(upcoming, open) )hi both
I think I am confused now. I tried both formula I am getting different results
=LET(
names, Data!$C$2:$C$172,
status, Data!$I$2:$I$172,
upcoming, UNIQUE(FILTER(names, status="Upcoming")),
closed, UNIQUE(FILTER(names, status="Closed")), SUM( ISNA( XMATCH( upcoming, closed ) )*1 )
)leads to 30
while
=COUNTA(UNIQUE(FILTER(Data!$C$2:$C$172,COUNTIFS(Data!$C$2:$C$172,Data!$C$2:$C$172,Data!$I$2:$I$172,"Closed")=0)))
leads to 31
can you please help me understand this difference? Note: I can not visually tell because the data is a lot
hi Serge and Harun
thank you for your efforts but I am a bit confused. Tried both your formulas and ended with different results. Since I dont know the exact answer, I dont know which one of those 2 formulas are accurate.
When I wrote:
=LET(
names, Data!$C$2:$C$172,
status, Data!$I$2:$I$172,
upcoming, UNIQUE(FILTER(names, status="Upcoming")),
closed, UNIQUE(FILTER(names, status="Closed")), SUM( ISNA( XMATCH( upcoming, closed ) )*1 )
)
I got result 30when I wrote:
=COUNTA(UNIQUE(FILTER(Data!$C$2:$C$172,COUNTIFS(Data!$C$2:$C$172,Data!$C$2:$C$172,Data!$I$2:$I$172,"Closed")=0)))
I got result 31.
Can you please help understand this difference?
