SUMIF

[Guest]

I am trying to use multiple criteria for SUMIF. Let's say I have a bunch of
numbers in colum A. I am trying to identify the ones that are between 2000
and 10000, multiply those numbers with the corresponding numbers in column B,
and then add those numbers up.
I hope that makes sense. I just can't get it to work.
Thanks.

 

[Guest]

=SUMPRODUCT(--(A1:A100>2000),--(A1:A100<10000),B1:B100)

It will not work if you use a whole column ex: A:A or B:B

 

[Guest]

That works great. Let me add a task.
What if I have 3 columns (A,B,C). I am looking for items between 2000 and
10000 in column A (like before). This time, for the items that are between
that range, I want to multiply those numbers with the numbers in the
corresponding B column, devide that by the numbers in the corresponding C
column, and then adding them up.
Basically I want to know how to incorporate a division in that formula.
Again, I hope that makes sense.
Thanks.

 

[Bob Phillips]

=SUMIF(A:A,">=1000",B:B)-SUMIF(A:A,">2000",B:B)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

That should be

=SUMIF(A:A,">=2000",B:B)-SUMIF(A:A,">10000",B:B)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

=SUM(IF((A1:A100>=2000)*(A1:A100<10000)*(ISNUMBER(C1:C100)),B1:B100/C1:C100)
)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)