• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.
 
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
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:
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
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.IMG_20220624_225839.png
View attachment 79596
 
Last edited:
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:
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

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
79601
 

Attachments

  • CountYes(BY).xlsx
    22.8 KB · Views: 4
Last edited:
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?
 
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"))

79616

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
 
Out of curiosity, which result would you expect for record 6?

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!
 

Attachments

  • conditional counts.xlsx
    11.2 KB · Views: 3
@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.
 
@Peter Bartholomew
Peter if you haven't seen this thread already it might be of interest.

 
Excel_beginner

use this formula

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