Sumif

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

If I wanted to sum values in Column D only if the values
are between 0 and 100, how would I do that?

Obviously what I have here isn't correct in this case.

=SUMIF(D:D,">0AND<100",D:D)
 
Bruce,

You could use two sumif's

=SUMIF(D:D,">0")-SUMIF(D:D,">=100")

OR a sumproduct

=SUMPRODUCT((D1:D6>0)*(D1:D6<100)*D1:D6)

Dan E
 
You could use the conditioanl sum wizard which is under
TOOLS>WIZARD>CONDITIONAL SUM or you can enter the formula
here and it should give your answer. If you can't find
the conditional sum wizard you may have to load it by
going to TOOLS>ADD-INS> and checking the Conditional sum
box.
=SUM(IF($d$1:$d$250>0,IF
($d$1:$d$250<100,$d$1:$d$250,0),0))

Joe
 
{=SUM(IF($D$10:$D$12>0,IF($D$10:$D$12<100,$D$10:$D$12,0),0))}

this is an array formula so when your are done, hit ctrl+shift+enter.

Should work..;)
 
One way:

=SUMIF(D:D,"<100",D:D)-SUMIF(D:D,"<0",D:D)

Change cell references as necessary.

MRO
 
Back
Top