Criteria issues...

Iniquitus

New Member
Joined
Nov 1, 2005
Messages
5
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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?
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top