sumif multiple criteria, some same cell
 

Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000 and
less than 7140999. Next identify only numbers that end in 150. Next check
column B and identify on the GL-2 cells...finally add the values in column C.
So the answer here should be $900. Thanks

sumif multiple criteria, some same cell
 

Hi,

Try this.

=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000
and
less than 7140999. Next identify only numbers that end in 150. Next check
column B and identify on the GL-2 cells...finally add the values in column
C.
So the answer here should be $900. Thanks

sumif multiple criteria, some same cell
 

Hi Ashish,
It works great!
Thanks very much

"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000
and
less than 7140999. Next identify only numbers that end in 150. Next check
column B and identify on the GL-2 cells...finally add the values in column
C.
So the answer here should be $900. Thanks

sumif multiple criteria, some same cell
 

Hi there,
I still need help....the number of lines I am evaluating may vary, so it may
be 400 lines on this sheet and 800 lines on another sheet. If I don't specify
the exact number of lines it gives me a #VALUE! error.
Any ideas? Thanks

"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000
and
less than 7140999. Next identify only numbers that end in 150. Next check
column B and identify on the GL-2 cells...finally add the values in column
C.
So the answer here should be $900. Thanks

sumif multiple criteria, some same cell
 

Hi there,
I don't have header row in this data set...tried it anyhow and it did not
work. Maybe I am doing something wrong....I get a #VALUE! error.

"Ashish Mathur" wrote:

Hi,

You can convert the range to a List (Ctrl+L). This makes the range auto
expanding. Before pressing Ctrl+L, please select the entire range including
the header row.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Hi there,
I still need help....the number of lines I am evaluating may vary, so it
may
be 400 lines on this sheet and 800 lines on another sheet. If I don't
specify
the exact number of lines it gives me a #VALUE! error.
Any ideas? Thanks

"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000
and
less than 7140999. Next identify only numbers that end in 150. Next
check
column B and identify on the GL-2 cells...finally add the values in
column
C.
So the answer here should be $900. Thanks

sumif multiple criteria, some same cell
 

If there are blank rows, you will get an error. I understood your question
as "how do I auto expand the range in the sumproduct when data gets added to
an existing range"

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Hi there,
I don't have header row in this data set...tried it anyhow and it did not
work. Maybe I am doing something wrong....I get a #VALUE! error.

"Ashish Mathur" wrote:

Hi,

You can convert the range to a List (Ctrl+L). This makes the range auto
expanding. Before pressing Ctrl+L, please select the entire range
including
the header row.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Hi there,
I still need help....the number of lines I am evaluating may vary, so
it
may
be 400 lines on this sheet and 800 lines on another sheet. If I don't
specify
the exact number of lines it gives me a #VALUE! error.
Any ideas? Thanks

"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than
7000000
and
less than 7140999. Next identify only numbers that end in 150. Next
check
column B and identify on the GL-2 cells...finally add the values in
column
C.
So the answer here should be $900. Thanks

sumif multiple criteria, some same cell
 

Any way around the blank rows....The formula you gave me works exactly how I
want it except for the blank rows. Thanks again.

"Ashish Mathur" wrote:

If there are blank rows, you will get an error. I understood your question
as "how do I auto expand the range in the sumproduct when data gets added to
an existing range"

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Hi there,
I don't have header row in this data set...tried it anyhow and it did not
work. Maybe I am doing something wrong....I get a #VALUE! error.

"Ashish Mathur" wrote:

Hi,

You can convert the range to a List (Ctrl+L). This makes the range auto
expanding. Before pressing Ctrl+L, please select the entire range
including
the header row.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Hi there,
I still need help....the number of lines I am evaluating may vary, so
it
may
be 400 lines on this sheet and 800 lines on another sheet. If I don't
specify
the exact number of lines it gives me a #VALUE! error.
Any ideas? Thanks

"Ashish Mathur" wrote:

Hi,

Try this.

=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim" wrote in message
...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than
7000000
and
less than 7140999. Next identify only numbers that end in 150. Next
check
column B and identify on the GL-2 cells...finally add the values in
column
C.
So the answer here should be $900. Thanks