SUMIF criteria - select a record with >, =, and <

[Guest]

This may be real basic - a basic syntax question.

I have records listed in rows. I have a column listing the age of a person
(column D). I want to select records on the basis of the range a person's age
falls into -- for example, ages 31 to 40. Once selected, I want to sum the
values in another column.

The following command is OK - selecting all those under age 31:
SUMIF(D1195,"<31",H1:H195)

I do not think this command is giving me an accurate count - selecting
records between 30 and 40 years of age:
SUMIF(D1195,">30*<41",H1:H195)

Am I on the right track?

 

[Dav]

Depending on if you mean < or <=

SUMIF(D1195,">30*<41",H1:H195)

SUMPRODUCT((D1195>30)*(D1195<41)*(H1:H195))

Should work

So I guess you were on the right lines

Regards

Dav

 

[Don Guillett]

try to count
=sumproduct((d1:d21>=31)*(d1:d21<40))
to sum
=sumproduct((d1:d21>=31)*(d1:d21<40),h1:h21)

 

[Bernard Liengme]

The best way is with SUMPRODUCT
=SUMPRODUCT(--(D1195>30),--(D1195<41),H1:H195)
For details see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

You could do it with SUMIF
=SUMIF(D1195,">30",H1:H195) - SUMIF(D1195,">40",H1:H195)

best wishes