| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
| SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
| Multiple Criteria for SUMIF | Excel Worksheet Functions | |||
| Sumif with Multiple Criteria | Excel Worksheet Functions | |||
| Sumif with multiple criteria | Excel Worksheet Functions | |||
Linear Mode
