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.

ajaydeshmukh

Copper Contributor

Jul 13, 2022

Martin_Weiss

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

Riny_van_Eekelen

Platinum Contributor

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.