Forum Discussion

Copper Contributor

Jul 13, 2022

Solved

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 o...

excel

Formulas and Functions

Macros and VBA

office 365

Riny_van_Eekelen
Jul 13, 2022
ajaydeshmukh
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.

Martin_Weiss

Bronze Contributor

Jul 13, 2022

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.

ajaydeshmukh

Copper Contributor

Jul 13, 2022

Hello, can you please help how to add countifs for yes or no or true or false

Martin_Weiss
Bronze Contributor
Jul 13, 2022
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"))
- ajaydeshmukh
  Copper Contributor
  Jul 14, 2022
  Martin_Weiss
  
  Please check. It ain't working. am I doing anything wrong ?
  - Riny_van_Eekelen
    Platinum Contributor
    Jul 14, 2022
    ajaydeshmukh But B1:B12 is empty, so COUNTIF doesn't find "Yes" anywhere.
- SergeiBaklan
  Diamond Contributor
  Jul 13, 2022
  Martin_Weiss
  
  Why with SUM ?
  - Martin_Weiss
    Bronze Contributor
    Jul 13, 2022
    Hi SergeiBaklan
    
    you are right, SUM is not necessary at all in this case. I just kept it because it was part of the initial formula that was posted.