# Criteria issues...

#### Iniquitus

##### New Member
So heres the situation. Im gonna be adding up a range of cells if they meet a certain criteria. The cell can either be a yes, or a no. I have the formula setup so that it counts the cells that say yes, and the cells that say no using COUNTIF's nested within IF's. But i also need to do a total of both yes and no's. How do i do that for the criteria of COUNTIF. i have tried doing COUNTIF(cell range, OR("Yes","No")), but that just returns true, and then wants to add if they are TRUE, but the cells only say yes and no, so i get a 0. Is there a way to check for both yes and no with only 1 criteria?

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to the board

If I understand you correctly....

=SUM(COUNTIF(cell range,{"Yes","No"}))

Thanks!

Tried the line you gave, and it only adds the first criteria it comes to in the { }. If i put {"Yes","No"} it only adds the yes, and if i put {"No","Yes"} it only adds the no's.

Are you sure?

works for me in this example
Book1
ABCD
1yes
2no
3hat
4no
5
6
73
8
Sheet4

hmm, i'm confused on how COUNTIF works. i took it as: it adds up all the cells that meet the criteria you specify. I'm confused on the need of COUNTIF to be within SUM. Shouldnt just COUNTIF by itself do the job?

=COUNTIF(range,{"yes","no"})

returns an array which, in my example, would be {1,2}

if you use this in one cell it will only show the first value you as you noticed, giving 1 in this case - so to give the correct count you need to add SUM to sum the values in the array

Awesome, thx man, got it working. That's weird cuz on my COUNTIF's i used for just yes or just no, i didnt have them in SUM, and it was giving the right value, not sure why, but i added sum to them and to the yes and no case and it all works. you da man.

You only need the sum if you have multiple criteria like "yes" and "no". If you only have one criterion you can use the basic COUNTIF as usual, e.g.

=COUNTIF(range,"yes")

Replies
6
Views
277
Replies
5
Views
520
Replies
14
Views
687
Replies
3
Views
159
Replies
12
Views
566

1,196,312
Messages
6,014,594
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back