| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Multiple Columns
I want to return a value based on column A & B, all the formulas I know or
have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
On Apr 25, 12:22 pm, "phuser" wrote:
I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
I would like to specify the column in the formula, there will be no user
interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
Hi,
Not sure if I have understood your need properly, but here's a suggestion. The following formula looks for 'Value1' in Column A, and 'Value2' in Column B. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
On Apr 25, 1:39 pm, "phuser" wrote:
I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
Thank you so Much Spikey, was able to do exactly what I was looking for and
more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
On Apr 30, 11:50 am, "phuser" wrote:
Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula. No problem. Just watch out if you have a lot of these. SUMPRODUCT is an array function by default, so it can slow everything down with too much calculation. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
Just when you think you got it all figured out, you need something else
*sigh* Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a 0.00 and I'm starting to think that the SUMPRODUCT does not work if you are using the same column. =SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(COACCT2008!$B$2:$B$3000=C21 ),COACCT2008!$C$2:$C$3000) Any other ideas? "phuser" wrote in message news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel... Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
Can the cells in column A be equal to A21 AND B21 at the same time?
(well, only if A21=B21). Do you mean equal to A21 OR equal to B21? If so, try this variation: =SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT 2008!$A$2:$A $3000=B21))*(CO*ACCT2008!$B$2:$B$3000=C21),COACCT2 008!$C$2:$C$3000) Here the + is equivalent to OR. Hope this helps. Pete On Apr 30, 11:41*pm, "phuser" wrote: *Just when you think you got it all figured out, you need something else *sigh* Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a 0.00 and I'm starting to think that the SUMPRODUCT does not work if you are using the same column. =SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(CO*ACCT2008!$B$2:$B$3000=C2 1),COACCT2008!$C$2:$C$3000) Any other ideas? "phuser" wrote in message news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel... Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$300*0=A5)*(COACCT2008!$B$2:$ B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODU C*T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2: $B$3000=B5),COACCT2008!$C$2*:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message .... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) * * * GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR * * * 4010 *0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 * * * 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 * * * 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
I am kind of thinking there should be a sumif in there somewhere because it
needs to add 2 values from 1 column eg. I have this Formula on a different sheet that does not include the reference to column c. =SUMIF(COACCT2008!$A$2:$A$3000,A43,COACCT2008!$AH$ 2:$AH$3000)+SUMIF(COACCT2008!$A$2:$A$3000,B43,COAC CT2008!$AH$2:$AH$3000))) "Pete_UK" wrote in message ... Can the cells in column A be equal to A21 AND B21 at the same time? (well, only if A21=B21). Do you mean equal to A21 OR equal to B21? If so, try this variation: =SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT 2008!$A$2:$A $3000=B21))*(CO*ACCT2008!$B$2:$B$3000=C21),COACCT2 008!$C$2:$C$3000) Here the + is equivalent to OR. Hope this helps. Pete On Apr 30, 11:41 pm, "phuser" wrote: Just when you think you got it all figured out, you need something else *sigh* Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a 0.00 and I'm starting to think that the SUMPRODUCT does not work if you are using the same column. =SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(CO*ACCT2008!$B$2:$B$3000=C2 1),COACCT2008!$C$2:$C$3000) Any other ideas? "phuser" wrote in message news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel... Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$300*0=A5)*(COACCT2008!$B$2:$ B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODU C*T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2: $B$3000=B5),COACCT2008!$C$2*:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula.- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
this returns a #value
on the right track though? =SUMPRODUCT(COACCT2008!$B$2:$B$3000=C28)*(SUMIF(CO ACCT2008!$A$2:$A$3000,A28,COACCT2008!C2:C3000)+(CO ACCT2008!$A$2:$A$3000,B28,COACCT2008!$C$2:$C$3000) ) "Pete_UK" wrote in message ... Can the cells in column A be equal to A21 AND B21 at the same time? (well, only if A21=B21). Do you mean equal to A21 OR equal to B21? If so, try this variation: =SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT 2008!$A$2:$A $3000=B21))*(CO*ACCT2008!$B$2:$B$3000=C21),COACCT2 008!$C$2:$C$3000) Here the + is equivalent to OR. Hope this helps. Pete On Apr 30, 11:41 pm, "phuser" wrote: Just when you think you got it all figured out, you need something else *sigh* Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a 0.00 and I'm starting to think that the SUMPRODUCT does not work if you are using the same column. =SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(CO*ACCT2008!$B$2:$B$3000=C2 1),COACCT2008!$C$2:$C$3000) Any other ideas? "phuser" wrote in message news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel... Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$300*0=A5)*(COACCT2008!$B$2:$ B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODU C*T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2: $B$3000=B5),COACCT2008!$C$2*:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula.- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
This is getting freakier by the try, now I get an actual amount maybe it's
the total COGS *sigh* Amount returned should be 1514.41 but Im getting 17,907,115.63 =SUMPRODUCT(--(COACCT2008!$B$2:$B$3000=C28))*(SUMIF(COACCT2008!$ A$2:$A$3000,A28,COACCT2008!C2:C3000)+(SUMIF(COACCT 2008!$A$2:$A$3000,B28,COACCT2008!$C$2:$C$3000))) "phuser" wrote in message news:a_mdnauXIN_ffITVnZ2dnUVZ_gSdnZ2d@sasktel... I am kind of thinking there should be a sumif in there somewhere because it needs to add 2 values from 1 column eg. I have this Formula on a different sheet that does not include the reference to column c. =SUMIF(COACCT2008!$A$2:$A$3000,A43,COACCT2008!$AH$ 2:$AH$3000)+SUMIF(COACCT2008!$A$2:$A$3000,B43,COAC CT2008!$AH$2:$AH$3000))) "Pete_UK" wrote in message ... Can the cells in column A be equal to A21 AND B21 at the same time? (well, only if A21=B21). Do you mean equal to A21 OR equal to B21? If so, try this variation: =SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT 2008!$A$2:$A $3000=B21))*(CO*ACCT2008!$B$2:$B$3000=C21),COACCT2 008!$C$2:$C$3000) Here the + is equivalent to OR. Hope this helps. Pete On Apr 30, 11:41 pm, "phuser" wrote: Just when you think you got it all figured out, you need something else *sigh* Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a 0.00 and I'm starting to think that the SUMPRODUCT does not work if you are using the same column. =SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(CO*ACCT2008!$B$2:$B$3000=C2 1),COACCT2008!$C$2:$C$3000) Any other ideas? "phuser" wrote in message news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel... Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$300*0=A5)*(COACCT2008!$B$2:$ B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODU C*T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2: $B$3000=B5),COACCT2008!$C$2*:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula.- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Columns
Thanks for all the help everyone, I got it from just playing around with
Pete's advise, Your all the best and could never do this wonderful thing called excel "phuser" wrote in message news:suadne8KotyoeYTVnZ2dnUVZ_uGdnZ2d@sasktel... this returns a #value on the right track though? =SUMPRODUCT(COACCT2008!$B$2:$B$3000=C28)*(SUMIF(CO ACCT2008!$A$2:$A$3000,A28,COACCT2008!C2:C3000)+(CO ACCT2008!$A$2:$A$3000,B28,COACCT2008!$C$2:$C$3000) ) "Pete_UK" wrote in message ... Can the cells in column A be equal to A21 AND B21 at the same time? (well, only if A21=B21). Do you mean equal to A21 OR equal to B21? If so, try this variation: =SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT 2008!$A$2:$A $3000=B21))*(CO*ACCT2008!$B$2:$B$3000=C21),COACCT2 008!$C$2:$C$3000) Here the + is equivalent to OR. Hope this helps. Pete On Apr 30, 11:41 pm, "phuser" wrote: Just when you think you got it all figured out, you need something else *sigh* Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a 0.00 and I'm starting to think that the SUMPRODUCT does not work if you are using the same column. =SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(CO*ACCT2008!$B$2:$B$3000=C2 1),COACCT2008!$C$2:$C$3000) Any other ideas? "phuser" wrote in message news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel... Thank you so Much Spikey, was able to do exactly what I was looking for and more :-) my version of your formula =IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$300*0=A5)*(COACCT2008!$B$2:$ B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODU C*T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2: $B$3000=B5),COACCT2008!$C$2*:$C$3000*-1))) works like a charm!! "Spiky" wrote in message ... On Apr 25, 1:39 pm, "phuser" wrote: I would like to specify the column in the formula, there will be no user interaction and it is a financial schedule, and I do have another worksheet that adds up fields but I was able to get that one easily because I only had to match field A to A using SUMIF, what I am unable to do is write up the formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc. Make sense? "Spiky" wrote in message ... On Apr 25, 12:22 pm, "phuser" wrote: I want to return a value based on column A & B, all the formulas I know or have found only allow for a value to be returned when the amount is based in 1 column but as you can see below I have LY, Budget & TY, for each month, I need to specify the column to be returned. This is one of the formula's I've tried. =INDEX(COACCT2008!$C$2:$AL$3000, MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0)) GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET DEC_CURRENT JAN_1YR 4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44 4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Need more info I think. Like... How do you want to specify the column to retrieve data from? Will you type it in once? Or does it require some user input or some other changing reference? Do you want a different formula to look up each column, like in a financial schedule of some kind? Ever need to add multiple columns? I guess....what exactly are you trying to create? I think this works. =SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12) This chooses your account from column A, your dept from column B, then returns the item in column C. You can adjust the formula to do one for each column you want to reference by changing the C1:C12 range. If the account numbers are text, put quotes around it instead of just the number (so: "4010"). Also, the ranges must be identical in size. So, A1:A12 and B2:B12 will not work in the same formula.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Data in Multiple Columns | Excel Discussion (Misc queries) | |||
| Index/Match from multiple columns | Excel Worksheet Functions | |||
| I need to match multiple columns before returning a value | Excel Discussion (Misc queries) | |||
| Vlookup/match/offset over multiple columns of lable | Excel Discussion (Misc queries) | |||
| HOW TO MATCH MULTIPLE COLUMNS WITH OR WITHOUT GAPS IN eXCEL ? | Excel Worksheet Functions | |||
![phuser[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
