Using countifs with Index function and (OR) criteria

Question

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.

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