Can someone please help me with a simple formula.

  • Thread starter Thread starter Bob
  • Start date Start date
B

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.
 
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
 
Hi

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

=COUNTIF(A1:A300,">100")-COUNTIF(A1:A300,">200")
 
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)
 
Back
Top