# Using countifs with Index function and (OR) criteria

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*"})))
``````

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

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.

• It worked after capitalizing the word "SICKL" . Great Jan 26, 2021 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. Jan 26, 2021 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? Jan 26, 2021 at 8:33
• The Boolean values TRUE and FALSE alone would return 0 inside SUM( ), because SUM ignores all data types other than real numbers. Jan 26, 2021 at 13:31