counts between two values

smithrd6

Board Regular
Joined
Dec 13, 2005
Messages
150
I have this forumula for capturing counts which are less than a number:

{=SUM((tbl42Ques!D2:D200="Released to Manufacturing")*(tbl42Ques!A2:A200<16))}

I am wanting to capture counts which are between two numbers, for instance, <16 and >10
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Try this (my example uses simplified cell references - adapt as required)

Code:
=sumproduct(d2:d200,--(a2:a200<16),--(a2:a200>10))
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.

=SUMPRODUCT(--(tbl42Ques!D2:D200="Released to Manufacturing"), --(tbl42Ques!A2:A200>10), --(tbl42Ques!A2:A200<16))
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
There's a difference between these two formulas.

Mine is summing up numeric values in column D, for rows where the entry in column A matches the criteria.

Norie's version (I think) is counting those values (which looks like it may be closer to what was requested in the OP).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows
Gerald

Good spot - think I missed the SUM part, though the OP did mention a count.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,964
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top