| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula
I have taken a qry from access and exported it into excel and am now trying
to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
All you need is sumif
=sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Jason Lepack,
Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07,qr y_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Sumif doesn't work for multiple conditions, but you can use sumproduct.
=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSION_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*DGET_INFO!B2:B30000,07,q ry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Okay I copied the formual below and it takes it but it does not give me the
amount. The amount is in the O2:O30000 column. Am I doing something wrong. It is giving me a zero or an O value. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSION_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*DGET_INFO!B2:B30000,07, qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Hi Jason
Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSION_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*DGET_INFO!B2:B30000,07, qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
For sure, email me at the address in my profile.
First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIO*N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU**DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU**DGET_INFO!B2:B30000,07, qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Jason,
Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100s (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e.g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIOÂ*N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*DGET_INFO!B2:B30000,0 7,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
No, I don't check that at work.
I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_*BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_*BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e.g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIO**N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU***DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU***DGET_INFO!B2:B30000,07 ,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Jason
Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e.g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIOÂ*Â*N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*DGET_INFO!O2:O30000 ) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*DGET_INFO!B2:B30000 ,07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Change:
qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100 To: or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300) On Jan 26, 12:43 pm, dnorthcutt wrote: Jason Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_**BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_**BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e..g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIO***N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU****DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU****DGET_INFO!B2:B30000,0 7,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
I tried your way from your previous post but it will not work.
Here is something that does work for just one criteria=ABC overseas =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD",qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) but the following will not work: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD" OR "DEF TRAVEL SERVICES",qry_FY06_COMMISSION_BUDGET_INFO!O2:O3000 0) I need both ABC overseas travel and DEF travel services (vendors)to give me a dollar total from column O. I used the 900 and 100 scenario just for quicker typing. Both ABC and DEF vendors are in column P. Really I have about 5 vendor names that I have to add for column P. I figured giving you an example for 2 would work and I could just duplicate what you told me to do. thanks, darlene "Jason Lepack" wrote: Change: qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100 To: or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300) On Jan 26, 12:43 pm, dnorthcutt wrote: Jason Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*Â*BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*Â*BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e..g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIOÂ*Â*Â*N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*Â*DGET_INFO!O2:O300 00) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*Â*DGET_INFO!B2:B300 00,07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
=SUMPRODUCT((qry_FY06_COMMISSION_BUDGET_INFO!B2:B4
="07")*((qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="AB C TRAVEL SERV OVERSEA LTD")+(qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="DEF TRAVEL SERVICES" ))*(qry_FY06_COMMISSION_BUDGET_INFO!O2:O4)) On Jan 26, 3:58 pm, dnorthcutt wrote: I tried your way from your previous post but it will not work. Here is something that does work for just one criteria=ABC overseas =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD",qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) but the following will not work: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD" OR "DEF TRAVEL SERVICES",qry_FY06_COMMISSION_BUDGET_INFO!O2:O3000 0) I need both ABC overseas travel and DEF travel services (vendors)to give me a dollar total from column O. I used the 900 and 100 scenario just for quicker typing. Both ABC and DEF vendors are in column P. Really I have about 5 vendor names that I have to add for column P. I figured giving you an example for 2 would work and I could just duplicate what you told me to do. thanks, darlene "Jason Lepack" wrote: Change: qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100 To: or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300) On Jan 26, 12:43 pm, dnorthcutt wrote: Jason Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_***BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_***BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e..g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIO****N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*****DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*****DGET_INFO!B2:B30000, 07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Jason
What is wrong with the formula below, I am not looking for the month in the b column anymore I am looking a dollar total from column O for one vendor(with their names spelled 4 different ways) in only column P. =SUM((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000="A BC TRAVEL SERV OVERSEA LTD"))+((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000 ="ABC TRAVEL SERVICES LTD"))+((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000 ="ABC TRAVEL SERVICES OVERSEA"))*((qry_FY06_COMMISSION_BUDGET_INFO!O2:O3 0000)) thanks, darlene "Jason Lepack" wrote: =SUMPRODUCT((qry_FY06_COMMISSION_BUDGET_INFO!B2:B4 ="07")*((qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="AB C TRAVEL SERV OVERSEA LTD")+(qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="DEF TRAVEL SERVICES" ))*(qry_FY06_COMMISSION_BUDGET_INFO!O2:O4)) On Jan 26, 3:58 pm, dnorthcutt wrote: I tried your way from your previous post but it will not work. Here is something that does work for just one criteria=ABC overseas =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD",qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) but the following will not work: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD" OR "DEF TRAVEL SERVICES",qry_FY06_COMMISSION_BUDGET_INFO!O2:O3000 0) I need both ABC overseas travel and DEF travel services (vendors)to give me a dollar total from column O. I used the 900 and 100 scenario just for quicker typing. Both ABC and DEF vendors are in column P. Really I have about 5 vendor names that I have to add for column P. I figured giving you an example for 2 would work and I could just duplicate what you told me to do. thanks, darlene "Jason Lepack" wrote: Change: qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100 To: or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300) On Jan 26, 12:43 pm, dnorthcutt wrote: Jason Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*Â*Â*BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*Â*Â*BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e..g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIOÂ*Â*Â*Â*N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*Â*Â*DGET_INFO!O2:O3 0000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*Â*Â*DGET_INFO!B2:B3 0000,07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Look up how to use the formulas that I have given you so far.
SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html SUMIF http://techonthenet.com/excel/formulas/sumif.php Cheers, Jason Lepack On Jan 26, 5:20 pm, dnorthcutt wrote: Jason What is wrong with the formula below, I am not looking for the month in the b column anymore I am looking a dollar total from column O for one vendor(with their names spelled 4 different ways) in only column P. =SUM((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000="A BC TRAVEL SERV OVERSEA LTD"))+((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000 ="ABC TRAVEL SERVICES LTD"))+((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000 ="ABC TRAVEL SERVICES OVERSEA"))*((qry_FY06_COMMISSION_BUDGET_INFO!O2:O3 0000)) thanks, darlene "Jason Lepack" wrote: =SUMPRODUCT((qry_FY06_COMMISSION_BUDGET_INFO!B2:B4 ="07")*((qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="AB C TRAVEL SERV OVERSEA LTD")+(qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="DEF TRAVEL SERVICES" ))*(qry_FY06_COMMISSION_BUDGET_INFO!O2:O4)) On Jan 26, 3:58 pm, dnorthcutt wrote: I tried your way from your previous post but it will not work. Here is something that does work for just one criteria=ABC overseas =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD",qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) but the following will not work: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD" OR "DEF TRAVEL SERVICES",qry_FY06_COMMISSION_BUDGET_INFO!O2:O3000 0) I need both ABC overseas travel and DEF travel services (vendors)to give me a dollar total from column O. I used the 900 and 100 scenario just for quicker typing. Both ABC and DEF vendors are in column P. Really I have about 5 vendor names that I have to add for column P. I figured giving you an example for 2 would work and I could just duplicate what you told me to do. thanks, darlene "Jason Lepack" wrote: Change: qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100 To: or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300) On Jan 26, 12:43 pm, dnorthcutt wrote: Jason Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_***BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_***BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e..g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIO****N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*****DGET_INFO!O2:O30000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*****DGET_INFO!B2:B30000, 07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula
Jason,
Sorry I have not gotten back to you recently. I got pulled off onto another aspect of work. I will do what you request below and look at the formulas you have given me so far. And I want to thank you again for all your help in this formula matter. I hope it is okay if I contact you again in the future, you have really been great with all your help. thanks so much, darlene "Jason Lepack" wrote: Look up how to use the formulas that I have given you so far. SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html SUMIF http://techonthenet.com/excel/formulas/sumif.php Cheers, Jason Lepack On Jan 26, 5:20 pm, dnorthcutt wrote: Jason What is wrong with the formula below, I am not looking for the month in the b column anymore I am looking a dollar total from column O for one vendor(with their names spelled 4 different ways) in only column P. =SUM((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000="A BC TRAVEL SERV OVERSEA LTD"))+((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000 ="ABC TRAVEL SERVICES LTD"))+((qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000 ="ABC TRAVEL SERVICES OVERSEA"))*((qry_FY06_COMMISSION_BUDGET_INFO!O2:O3 0000)) thanks, darlene "Jason Lepack" wrote: =SUMPRODUCT((qry_FY06_COMMISSION_BUDGET_INFO!B2:B4 ="07")*((qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="AB C TRAVEL SERV OVERSEA LTD")+(qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="DEF TRAVEL SERVICES" ))*(qry_FY06_COMMISSION_BUDGET_INFO!O2:O4)) On Jan 26, 3:58 pm, dnorthcutt wrote: I tried your way from your previous post but it will not work. Here is something that does work for just one criteria=ABC overseas =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD",qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) but the following will not work: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA LTD" OR "DEF TRAVEL SERVICES",qry_FY06_COMMISSION_BUDGET_INFO!O2:O3000 0) I need both ABC overseas travel and DEF travel services (vendors)to give me a dollar total from column O. I used the 900 and 100 scenario just for quicker typing. Both ABC and DEF vendors are in column P. Really I have about 5 vendor names that I have to add for column P. I figured giving you an example for 2 would work and I could just duplicate what you told me to do. thanks, darlene "Jason Lepack" wrote: Change: qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100 To: or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900, qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300) On Jan 26, 12:43 pm, dnorthcutt wrote: Jason Me again......... You have no idea how you have helped me, thank you so very much. It all finally worked. Now if you are game I have another request. How would I write the formula if I wanted to pick out more than one certain data in the same colum e.g., before I needed all the 100's in column H but now I want all the 100's, 900's and the 300's in column H, not just the 100's. I have worked and worked with the formula you gave me for just the 100's but I keep getting various error messages no matter how I change it. I guess was hoping that I could chance my way to the correct answer but that just does'nt seem to be working.............lol. Again if you can help me great, if I am getting bothersome just let me know.....lol. thanks, darlene "Jason Lepack" wrote: No, I don't check that at work. I'm located near Ottawa, Canada. Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*Â*Â*BUDGET_INFO!O2:O30000)) Take out the leading apostrophe in the "'07" in your formula. The ' is not a part of the string, it's a text qualifier. If that doesn't fix it then try below. In four seperate cells place these four fomulas: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07")) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)) =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100")) The criteria at the end of the statements that return numbers other than zero are the two criteria that you need in your formula. Cheers, Jason Lepack On Jan 25, 9:51 am, dnorthcutt wrote: Jason, Will you be able to answer during the day from you gmail account? If you can see emails during the day I will switch and email you at gmail. If not, here is what I wanted to write to you: Column B looks to be text, it is a number but has the green corner. Column H is also a number but has the green corner, and column O is the number, no green corner. Results needed: I need the dollar sum of column O, by all the 100's (budget code) in column H, by the column B (the month-07). Here is my formula: =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0 7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_Â*Â*Â*BUDGET_INFO!O2:O30000)) Again, thanks so much for all your help. BTW where are you located e..g., US, Ohio England, just curious. thanks, darlene "Jason Lepack" wrote: For sure, email me at the address in my profile. First glance makes me wonder how your Columns are formatted. I have assumed that column B is formatted as text and that H was formatted as a number. I'll be home in an hour. Cheers, Jason Lepack On Jan 24, 4:21 pm, dnorthcutt wrote: Hi Jason Can we talk direct via email without having to go through this online discussion. That would really help formula errors communication. Just let me know. thanks, darlene "Jason Lepack" wrote: Sumif doesn't work for multiple conditions, but you can use sumproduct. =SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 = "07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIOÂ*Â*Â*Â*N_BUDGET_INFO!O2:O30000)) Remember that the above formula will wrap, copy it all. Cheers, Jason Lepack On Jan 24, 11:28 am, dnorthcutt wrote: Jason Lepack, Thank you so much for that info, it worked. Now I tried to add something to the sumiff but it is giving me a compile error=expected:line number,or label, or statement or end of statement. Here is what I put in originall and it worked: =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*Â*Â*DGET_INFO!O2:O3 0000) this is giving me the sum for all 900 entries Now I tried to add another parameter and it gives me the above error. =SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BUÂ*Â*Â*Â*Â*DGET_INFO!B2:B3 0000,07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000) qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another paramenter. I needed all 900 amounts that were 07 Any help you can give me in this is greatly appreciated. "Jason Lepack" wrote: All you need is sumif =sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd) Cheers, Jason Lepack dnorthcutt wrote: I have taken a qry from access and exported it into excel and am now trying to come up with a formula in excel. I have 3 sheets in my workbook and I am trying to come up with a one cell sum from the data in one of the other sheets. My conditions are if this column in sheet a=(this sheet has a very long name) has a 900 in it then take the figure from this other column in sheet a and sum it into this column in sheet b. There are many figures in one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
