Forum Discussion
SUM plus countifs
Hello,
I hope everyone is doing fine.
I have used SUM + COUNTIFS together with range. I have got the result but I want to add yes/no or true/false in front of every row to to represent data of every under above formula of (SUM+Countifs (range)).
Your formula counts all numbers <=13, being 4
plus
the count of all numbers >= 17, being 5.
Thus, 9
Putting the criteria in curly brackets forces an OR rather than the AND type of function you need. Count all number >= 13 AND <= 17.
Try it this way:
=COUNTIFS(A1:A12,">=13",A1:A12,"<=17") resulting in 7.
18 Replies
Hi ajaydeshmukh
could you please post an example of your file, what it should look like? This makes it easier for us to give proper advise.
Generally spoken, you could use more criteria in COUNTIFS. So it would be possible, to add one criteria to count yes/no or true/false.
- Hello, can you please help how to add countifs for yes or no or true or false
Hi ajaydeshmukh
I take Riny_van_Eekelen proposal as a base. You just need to add another criteria range + criteria.
=SUM(COUNTIFS(A1:A12,">=13",A1:A12,"<=17",B1:B12,"yes"))
formula used - =SUM(COUNTIFS(A1:A12,{"<=13",">=17"}))
1. Why is it showing result 9, but not 7?
2. I want to have filter in B column stating if values in rows are following the criterion of formula in c1 or not? eg all 13, 14, 17 could have yes or true in next cell. rest all would be no or false
thanks
Your formula counts all numbers <=13, being 4
plus
the count of all numbers >= 17, being 5.
Thus, 9
Putting the criteria in curly brackets forces an OR rather than the AND type of function you need. Count all number >= 13 AND <= 17.
Try it this way:
=COUNTIFS(A1:A12,">=13",A1:A12,"<=17") resulting in 7.
