# How to combine IF with COUNTIF

#### Excel_beginner

##### New Member
Hello friends!
My first post here.
I have 8 columns containing values yes or no. However, there are also blank values in many cases.
My aim is to count the numbers of times "yes" occurs.
When applying =COUNTIF(A2:H2;"yes") the formula shows = 0 when A2:H2 is blank.
This is not good since it also shows =0 when A2:H2 is ="no".

I need somehow to separate true 0 (i.e ="no") from blanks, which are set to 0. I believe I need to combine IF with COUNTIF, but I'm not sure how to write the formula. Can anyone pleas help? Thanks in advance.

#### vletm

##### Excel Ninja
Excel_beginner
If the whole range is blank then there are none "yes" ... then 0 is okay.
as well as with "no" ... there are none "no".
Yours: My aim is to count the numbers of times "yes" occurs.
If You write eg to cell C2 yes, then Your formula shows ... 1.

#### Excel_beginner

##### New Member
Excel_beginner
If the whole range is blank then there are none "yes" ... then 0 is okay.
as well as with "no" ... there are none "no".
Yours: My aim is to count the numbers of times "yes" occurs.
If You write eg to cell C2 yes, then Your formula shows ... 1.
Thank you for the reply. Unfortunately, it's not what I want as that will bias the result.
The aim is to count the number of services a person has subscribed to, and those who have true ="no" should be getting =0.
The blanks should never get 0.

Last edited:

#### vletm

##### Excel Ninja
Excel_beginner
The blanks should never get 0.
Did You count .... blanks?
Could You send a sample Excel-file, which shows - what do You really mean?

#### KenU

##### Active Member
Try the variation below which will give the counts of yes, no and blanks respectively.

Depending on your version of Excel, this will spill automatically (365) or need to be array entered by selecting three adjacent cells (such as A1:C1) and confirming with ctrl + shift + enter.

=COUNTIF(A2:H2,{"yes","no",""})

Hope that helps.

Regards,
Ken

#### Excel_beginner

##### New Member
Excel_beginner
The blanks should never get 0.
Did You count .... blanks?
Could You send a sample Excel-file, which shows - what do You really mean?
Imagine the data looks as in the screen shot below. Row 3 is blank in the entire range.
Imagine now a 4th column where I want to sum the number of times "Yes" occurs. For row 1 it should be =1
For row 2 =2
For row 3 it should be blank and not 0
For row 4 it show = 3
I hope it's clear now.
View attachment 79596

Last edited:

#### Peter Bartholomew

##### Well-Known Member
A formula
= TEXTJOIN(" out of ",,COUNTIF(range,{"yes";"<>"}))
might return "2 out of 6" for example.

#### Peter Bartholomew

##### Well-Known Member
That is somewhat different!

#### Excel_beginner

##### New Member
That is somewhat different!
View attachment 79598
I want it to return a numerical value where not blank, so even with your brilliant solution it still shows 0.

The closest thing removing the 0s from blanks was=if(COUNTIF(Range,"No")=0,"",COUNTIF(Range"Yes"))

But it didn't work properly. When the entire range is ="Yes", excel returns blanks, while it should return=3 in this case.

Last edited:

#### bosco_yip

##### Excel Ninja
Try,

1] In D2, formula copied down :

=IF(COUNTIF(A2:C2,"><"),COUNTIF(A2:C2,"Yes"))

Remark :

a. Change cell range to A2:H2 or to suit your actual form

b. Change "," to ";" in meet with your country computer setting

Then,

In D2 >> Conditional Formatting >>
• Select "Format only only cells that contain"
• In 2nd box >> format only cells with >> choose "Cell Value"
• >> Choose "equal to"
• >> Enter "=FALSE"
• Click " Format..." >> Choose cell font color to "white" color
• OK

#### Attachments

• 22.8 KB Views: 3
Last edited:

#### Excel_beginner

##### New Member
Try,

1] In D2, formula copied down :

=IF(COUNTIF(A2:C2,"><"),COUNTIF(A2:C2,"Yes"))

Remark :

a. Change cell range to A2:H2 or to suit your actual form

b. Change "," to ";" in meet with your country computer setting

View attachment 79600

Then,

In D2 >> Conditional Formatting >>
• Select "Format only only cells that contain"
• In 2nd box >> format only cells with >> choose "Cell Value"
• >> Choose "equal to"
• >> Enter "=FALSE"
• Click " Format..." >> Choose cell font color to "white" color
• OK
View attachment 79601
Thank you for this very pedagogical answer and taking the time to answer me.
I just have a question about the =IF(COUNTIF(A2:C2,"><")

What does that syntax do? Usually not equal to is written as <>. In the example you used ><. Basically, can I have an interpretation of the formula?

#### bosco_yip

##### Excel Ninja
Thank you for this very pedagogical answer and taking the time to answer me.
I just have a question about the =IF(COUNTIF(A2:C2,"><")

What does that syntax do? Usually not equal to is written as <>. In the example you used ><. Basically, can I have an interpretation of the formula?
A lot of ways can solve the problem

For example, another formula option in D2 copied down formula

=IF(COUNTBLANK(A2:C2),"",COUNTIF(A2:C2,"Yes"))

Explain of COUNTIF(A2:C2,"><")

=COUNTIF(A2:C2,"><") is count for number of non blank data, it is as same as =COUNTIF(A2:C2,"?*")

Thank you

Regards

#### Peter Bartholomew

##### Well-Known Member
Out of curiosity, which result would you expect for record 6?

The 365 formulas are
Code:
``````= BYROW(range,LAMBDA(row,
IF(AND(row<>""),COUNTIFS(row,"Yes"),"")
))

= BYROW(range, LAMBDA(row,
IF(OR(row<>""), COUNTIFS(row,"Yes"),"")
))``````
@bosco_yip I have certainly never seen "><" used before!

#### Attachments

• 11.2 KB Views: 2

#### Excel_beginner

##### New Member
Out of curiosity, which result would you expect for record 6?

View attachment 79619
The 365 formulas are
Code:
``````= BYROW(range,LAMBDA(row,
IF(AND(row<>""),COUNTIFS(row,"Yes"),"")
))

= BYROW(range, LAMBDA(row,
IF(OR(row<>""), COUNTIFS(row,"Yes"),"")
))``````
@bosco_yip I have certainly never seen "><" used before!
If the question is for me, then on row 6 I would expect =2.

#### bosco_yip

##### Excel Ninja
If the question is for me, then on row 6 I would expect =2.
If you have row 6 data, my revised formula in D2 become :

=IF(COUNTBLANK(A2:C2)=3,"",COUNTIF(A2:C2,"Yes"))

Remark : Change the above Red colored number to meet with your actual number of data columns

#### GraH - Guido

##### Well-Known Member
@Peter Bartholomew

Ha! Ha! I have some strange formula like this.

Regards

bosco_yip
@Peter Bartholomew , "><" aka the angry eyes. So I know stuff, you don't?

#### Peter Bartholomew

##### Well-Known Member
@bosco_yip @GraH - Guido
Thanks for that; I certainly hadn't come across it before! It appears that one should read the formula "><" as a string expressing the condition
> "<"
just as no blanks "<>" may be read
<> ""
Whether I remember it is another matter. I have the sort of memory that can run through 100 tips-and-tricks and remember the final six! I was never born to be a spreadsheet virtuoso (my French vocabulary bears the same hallmarks and, as for Dutch (Flemish?), I don't think I got beyond
"Dank u wel")!

Something else that forms a valid Excel expression that brought me up short was
= C4:E4:D10
That was another "er, what?" moment.

#### Hans Knudsen

##### New Member
@Peter Bartholomew
Peter if you haven't seen this thread already it might be of interest.

#### S P P

##### Member
Excel_beginner

use this formula

=IF(OR(A2<>"";B2<>"";C2<>"";D2<>"";E2<>"";F2<>"";G2<>"";H2<>"");COUNTIF(A2:H2;"Yes");"")