Google spreadsheet countif non empty cells except certain values

Question

So basically i want to check if certain range of field is not blank or has a certain value

so

=ArrayFormula(COUNTIF(NOT(ISBLANK(H24:I28)), true))

will count if they are blank

How can i edit it to also search for a certain value so far i've tried :

=ArrayFormula(COUNTIF(NOT(ISBLANK(H24:I28)) OR(IFERROR(SEARCH("someValue",H24:I28,1)>0,FALSE)), true))

Here is a link to a sheet as an example:

Just saw your sheet. You mention "Members count: 4" Which ones do you count? Canada, French, Palma, US? If so, what is the meaning of Empty 1, Empty 2? Please explain. — marikamitsos
You say "i want to check if certain range of field is not blank or has a certain value", but it is effectually the same as "i want to check if certain range of field is not blank" as having a certain value means it is not blank, thus making logical expression true. Maybe you want to count something else? — kishkin
Well i want to count every value except anything that starts with the word Empty basically — Neta Meta

marikamitsos marikamitsos · Accepted Answer · 2020-04-24T17:11:26

Well i want to count every value except anything that starts with the word Empty basically

=COUNTIF(H24:I29,"<>") - 
 COUNTIF(ArrayFormula(REGEXMATCH(H24:I29,"Empty")),TRUE)

Functions used:

To find whether theValue exists or not, you can use the following formula:

=IF(COUNTIF(H24:I28,"<>")>0,IF(COUNTIF(H24:I28,"someValue")>0,"someValue","no value"),"empty")

To count how many times theValue exists, please use:

=IF(COUNTIF(H24:I28,"<>")>0,IF(COUNTIF(H24:I28,"someValue")>0,COUNTIF(H24:I28,"someValue"),"no value"),"empty")

(Of course you can adjust the "messages" to your liking)

Functions used: