Forum Discussion

Deleted

Sep 19, 2022

Solved

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 mid...

excel

HansVogelaar
Sep 19, 2022
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))

HansVogelaar

MVP

Sep 19, 2022

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

Sep 19, 2022

thank you so much sir!
so my issue is the list of the name ranges from: ** elementary, to XX High school or && academy; middle school

HansVogelaar
MVP
Sep 19, 2022
Deleted

What exactly should be included, and what should be excluded?
- Deleted
  Sep 19, 2022
  I just sent you a message thx
  - HansVogelaar
    MVP
    Sep 19, 2022
    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))