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.

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

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


"gottahavit" wrote:

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.

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.

"Teethless mama" wrote:

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

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


"gottahavit" wrote:

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.

=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)

"gottahavit" wrote in message
...
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.

"Teethless mama" wrote:

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

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


"gottahavit" wrote:

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.

=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)

"gottahavit" wrote in message
...
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.

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)

"gottahavit" wrote in message
...
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.