Forum Discussion
how to use sum product count unique value with more condition? Many thx in advance!
Hi, all
I have a question to ask for a formula I plan to use in excel:
this is my situation:
1. in E column, we have 10k rows of different school name normally end with school, high school or middle school, for some cases we dont know the school name, we either name as unknown, Multiple, or USD xxx - Unknown
2. I planned to use sumproduct formula to know the unqiue value of all school name (also excluding name such as: unknown, Multiple, or USD xxx - Unknown. Normally I use remove duplicates to calculate the unique value, but this time I planned to use formula to count
3. So far my formula can only count unqiue value of all name, but cannot adding conditions to exclude some specific cases, could anybody help me?
Many thanks
Deleted
New formula:
=SUM(--(FREQUENCY(IF((UPPER(LEFT(A2:A8872,3))<>"USD")*(UPPER(A2:A8872)<>"UNKNOWN")*(UPPER(A2:A8872)<>"MULTIPLE")*(UPPER(A2:A8872)<>"NON-SEDGEWICK COUNTY-NOT SPECIFIED"),MATCH(A2:A8872,A2:A8872,0)),ROW(A2:A8872)-ROW(A2)+1)>0))
9 Replies
Deleted If you want to go with SUMPRODUCT() then could try-
=SUMPRODUCT(IFERROR(1/(COUNTIFS(A1:A15,A1:A15,A1:A15,"<>Unknown",A1:A15,"<>Multiple",A1:A15,"<>*USD*")),0))Otherwise can with new dynamic formulas.
=COUNTA(UNIQUE(FILTER(A1:A15,BYROW(A1:A15,LAMBDA(x,MAX(COUNTIFS(x,{"Unknown","Multiple","*USD*"}))))=0)))thank you sir, i tried your method but showing:
did I do sth wrong?
so the school list name ranges from ** elementary, to XX High school or && academy;
when I use sumproduct I want to remove the unique value of those condition:
1. name starting with USD
2. name = multiple
3. name = unknown
- Did you check my attached file? You formula looks fine. Can you attach a sample file which you getting wrong result? Also what is your excel version? In case of Microsoft-365 you can try second formula.
Deleted
If you have Microsoft 365 or Office 2021:
=COUNTA(UNIQUE(FILTER(D2:D10000,UPPER(RIGHT(D2:D10000,6))="SCHOOL","")))
If you have an older version, use the following formula, confirmed with Ctrl+Shift+Enter:
=SUM(--(FREQUENCY(IF(UPPER(RIGHT(D2:D10000,6))="SCHOOL",MATCH(D2:D10000,D2:D10000,0)),ROW(D2:D10000)-ROW(D2)+1)>0))
Deleted
What exactly should be included, and what should be excluded?
