0

I'm using this formula to count rows that has either of the following text (SCD,SCA,Sickl). First I indexed two columns to be one column (E & G) Then I asked to count any row that has any one of the following texts (SCD or SCA or sickl). if the column has two of three of those texts, It will all return same count 1 as one row only.

=ArrayFormula(sum(countif(index(admissions!E9:E58 & admissions!G9:G58),{"*SCD*","*SCA*","*Sickl*"})))

https://docs.google.com/spreadsheets/d/1OUk3hUwL2CcePR4aKXU746DQTVdTVE5_lICpYSKBLpg/edit?usp=sharing

This is the link, the formula gives back a result of 6 while it should have given 4 only.

1

I added a sheet ("Erik Help") with the following formula:

=ArrayFormula(SUM(1*REGEXMATCH(UPPER(admissions!E9:E58 & admissions!G9:G58),"SCD|SCA|SICKL")))

REGEXMATCH will see if any of the elements separated by the pipe symbol are in the search range.

UPPER assures uniformity (so that lowercase, mixed-case or uppercase matches will count as a match)

The results would normally return as a list of TRUE or FALSE (depending on if a match is found). So adding 1* is a way to turn those into 1s and 0s respectively.

SUM, of course, SUMS those 1s and 0s.

4
  • It worked after capitalizing the word "SICKL" . Great – Hassan Almaateeq Jan 26 at 3:04
  • Ah, yes, of course. I missed that "Sickl" wasn't originally all-caps. Glad you caught that. I fixed it in the post. – Erik Tyler Jan 26 at 3:58
  • What if I didn't add "1*" or in other words {REGEXMATCH(UPPER(admissions!E9:E58 & admissions!G9:G58),"SCD|SCA|SICKL")} what does it return as a result? – Hassan Almaateeq Jan 26 at 8:33
  • 1
    The Boolean values TRUE and FALSE alone would return 0 inside SUM( ), because SUM ignores all data types other than real numbers. – Erik Tyler Jan 26 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.