Can someone please help me with a simple formula.

[Bob]

Hi, I need to create a sumif formula that will output the number of
cells that fall between a range of 100 to 200. I tried using greater
than 100 and less 200 but it doesn't seem to work (maybe I entered it
wrong, I'm new at this).

Thanks for your help.

 

[Bernard Liengme]

Hi Bob,
SUMIF cannot cope with two criteria but a combination formula works:
SUMIF(A1:A1000,">=200") - SUMIF(A1:A1000,"<100")
Another way is with SUMPRODUCT;
=SUMPRODUCT(--(A1:A1000>=100),--(A1:A1000<=200),A1:A1000)
However, your message talks of 'out but the NUMBER so I think we should be
using COUNTIF not SUMIF:
COUNTIF(A1:A1000,">=200") - COUNTIF(A1:A1000,"<100")
or -- =SUMPRODUCT(--(A1:A1000>=100),--(A1:A1000<=200))
Best wishes
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

 

[Harald Staff]

Hi

Use COUNTIF for counting. SUMIF is summing the actial values.

=COUNTIF(A1:A300,">100")-COUNTIF(A1:A300,">200")

 

[Don Guillett]

to count
=sumproduct((a2:a22>100)*(a2:a22<200))
to sum another range with that criteria
=sumproduct((a2:a22>100)*(a2:a22<200)*b2:b22)

 

[Dana DeLouis]

Just to be different, that's all:

=SUMPRODUCT(0+(ABS(rng-150)<50))