syntax for countif when cells fall within a numerical range

[Tom L]

I have a column of numerical data, for which I need frequency counts within
certain ranges. E.G. If a cell value is >= 41 AND <= 50, I want to count
that cell as one. I can't figure out the syntax.

 

[sb1920alk]

Countif can only be used with one criteria.

Use Sumproduct instead. For example, if your list is in A1:A100, you could
use =SUMPRODUCT(--(A1:A100>=41),--(A1:A100<=50))

 

[Max]

I have a column of numerical data, for which I need frequency counts within
certain ranges. E.G. If a cell value is >= 41 AND <= 50, I want to count
that cell as one. I can't figure out the syntax.

One way, via sumproduct, eg:
=SUMPRODUCT((A2:A10>=41)*(A2:A10<=50))
Adapt the range to suit.

---

 

[Max]

Countif can only be used with one criteria.

Think we could use 2 countifs as another option:
=COUNTIF(A:A,">=41")-COUNTIF(A:A,">50")

---

 

[sb1920alk]

Ha, very cute. It's not really 'one' countif, but I like it anyways.

 

[T. Valko]

Countif can only be used with one criteria.

But you can use more criteria when nested inside another function like this:

=SUM(COUNTIF(A1:A20,{">=41",">50"})*{1,-1})

=SUM(COUNTIF(A1:A20,{"X","Y","Z"}))

G1:G3 = X, Y, Z

=SUMPRODUCT(COUNTIF(A1:A20,G1:G3))