Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Need an =if statement
What I need is to be able to add the units of each product seperately
in the Summary sheet, and get percentages. Essentially I need to be able to tell the Summary sheet the following: If any cell Column D in the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then add the number in Column C in the corresponding cell to the "running total". Note: Column D cells are a dropdown list (of various products) tied to the Summary page I have a sample spreadsheet I can show if it would help. ANY HELP would be GREATLY APPRECIATED.....as I have been working on this for over a week (off and on) |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Need an =if statement
Perhaps this 'hint' will give you a start, this assumes values in rows 1
through 4, change as required. It gives the total of all "Potato" entries in the range (word in D, quantity in C) =SUMPRODUCT(--(D1:D4="Potato"),C1:C4) there is almost always some help to be had at HelpFrom @ jlathamsite.com remove those spaces - if you really want help. " wrote: What I need is to be able to add the units of each product seperately in the Summary sheet, and get percentages. Essentially I need to be able to tell the Summary sheet the following: If any cell Column D in the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then add the number in Column C in the corresponding cell to the "running total". Note: Column D cells are a dropdown list (of various products) tied to the Summary page I have a sample spreadsheet I can show if it would help. ANY HELP would be GREATLY APPRECIATED.....as I have been working on this for over a week (off and on) |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Need an =if statement
Ok,
Here's what I tried (one of various) =SUMPRODUCT(=if'Order List'! D2:D50=potatos),(D2:D50="Potato"),(C2:C50) I think I must have misunderstood the formula you are showing me, because it didn't work. It says the formula I typed contains an error. I have to admit, I'm not very experienced with formulas, just basic stuff. My mind tells me the formula should (more or less) say: If any cell in column D on the Order List equals potatos add it here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. and, that I should be able to use the same formula (changing names of course) for the other products. The problem is, I really don't know how to write it in language excel will understand..... On Oct 12, 2:18 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Perhaps this 'hint' will give you a start, this assumes values in rows 1 through 4, change as required. It gives the total of all "Potato" entries in the range (word in D, quantity in C) =SUMPRODUCT(--(D1:D4="Potato"),C1:C4) there is almost always some help to be had at HelpFrom @ jlathamsite.com remove those spaces - if you really want help. " wrote: What I need is to be able to add the units of each product seperately in the Summary sheet, and get percentages. Essentially I need to be able to tell the Summary sheet the following: If any cell Column D in the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then add the number in Column C in the corresponding cell to the "running total". Note: Column D cells are a dropdown list (of various products) tied to the Summary page I have a sample spreadsheet I can show if it would help. ANY HELP would be GREATLY APPRECIATED.....as I have been working on this for over a week (off and on)- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Need an =if statement
Sorry, I am at work, and got interrupted when I posted this...just
realized I forgot an important element, it should say: If any cell in column D on the Order List equals potatos add the number in the corresponding cell in column D here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. On Oct 12, 10:25 am, boosbrde wrote: Ok, Here's what I tried (one of various) =SUMPRODUCT(=if'Order List'! D2:D50=potatos),(D2:D50="Potato"),(C2:C50) I think I must have misunderstood the formula you are showing me, because it didn't work. It says the formula I typed contains an error. I have to admit, I'm not very experienced with formulas, just basic stuff. My mind tells me the formula should (more or less) say: If any cell in column D on the Order List equals potatos add it here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. and, that I should be able to use the same formula (changing names of course) for the other products. The problem is, I really don't know how to write it in language excel will understand..... On Oct 12, 2:18 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Perhaps this 'hint' will give you a start, this assumes values in rows 1 through 4, change as required. It gives the total of all "Potato" entries in the range (word in D, quantity in C) =SUMPRODUCT(--(D1:D4="Potato"),C1:C4) there is almost always some help to be had at HelpFrom @ jlathamsite.com remove those spaces - if you really want help. " wrote: What I need is to be able to add the units of each product seperately in the Summary sheet, and get percentages. Essentially I need to be able to tell the Summary sheet the following: If any cell Column D in the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then add the number in Column C in the corresponding cell to the "running total". Note: Column D cells are a dropdown list (of various products) tied to the Summary page I have a sample spreadsheet I can show if it would help. ANY HELP would be GREATLY APPRECIATED.....as I have been working on this for over a week (off and on)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Need an =if statement
Try this:
I'm going to assume that cell A2 of your Summary Sheet contains: Potatoes In cell B2, enter the formula: =SUMPRODUCT(--('Order List'!D2:D50=A2),C2:C50) Then, assuming cell A3 of your Summary Sheet contains: Cookies Copy the formula in cell B2 down to B3, and you'll have your results for Cookies. Continue this with each value in Column A that you want to summarize. If your list of products starts somewhere other than A2, then adjust the formula accordingly. HTH, Elkar "boosbrde" wrote: Sorry, I am at work, and got interrupted when I posted this...just realized I forgot an important element, it should say: If any cell in column D on the Order List equals potatos add the number in the corresponding cell in column D here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. On Oct 12, 10:25 am, boosbrde wrote: Ok, Here's what I tried (one of various) =SUMPRODUCT(=if'Order List'! D2:D50=potatos),(D2:D50="Potato"),(C2:C50) I think I must have misunderstood the formula you are showing me, because it didn't work. It says the formula I typed contains an error. I have to admit, I'm not very experienced with formulas, just basic stuff. My mind tells me the formula should (more or less) say: If any cell in column D on the Order List equals potatos add it here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. and, that I should be able to use the same formula (changing names of course) for the other products. The problem is, I really don't know how to write it in language excel will understand..... On Oct 12, 2:18 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Perhaps this 'hint' will give you a start, this assumes values in rows 1 through 4, change as required. It gives the total of all "Potato" entries in the range (word in D, quantity in C) =SUMPRODUCT(--(D1:D4="Potato"),C1:C4) there is almost always some help to be had at HelpFrom @ jlathamsite.com remove those spaces - if you really want help. " wrote: What I need is to be able to add the units of each product seperately in the Summary sheet, and get percentages. Essentially I need to be able to tell the Summary sheet the following: If any cell Column D in the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then add the number in Column C in the corresponding cell to the "running total". Note: Column D cells are a dropdown list (of various products) tied to the Summary page I have a sample spreadsheet I can show if it would help. ANY HELP would be GREATLY APPRECIATED.....as I have been working on this for over a week (off and on)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
Need an =if statement
The system was unresponsive earlier, I sent you the following comments via
email direct along with a workbook with example formulas. I'll repeat the body of the email here. Feel free to respond to the email if you need more help. As Elkar has shown, you can also reference a cell with the word to be matched in it instead of the actual word. The email body: Youre close with your formula, but you need to keep things separated. A few things wrong with your formula as shown in your 2nd post at the site: =SUMPRODUCT(=if'Order List'! D2:D50=potatos),(D2:D50="Potato"),(C2:C50) The main problem is that you cant have the second = within the formula as you have it., plus you needed to enclose the word potatos within quotes (although that wont fix it all). Also your matching of ( and ) is incorrect. Assuming that your list of products and quantity is on sheet Order List, then the formula to return the total of orders for Potato would be =SUMPRODUCT(--(Order List!D2:D50=Potato),Order List!C2:C50) What happens here is that the first part of the formula, --(Order List!D2:D50=Potato) Looks at each row from 2 to 50, column D to see if it contains the word Potato (if it is actually Potatos, then change that in the formula). It returns a true/false indication for each row, and true is returned as -1, false is returned as 0. The -- in front of the test converts -1 to a positive 1 when the result is true. The result of that test (either 0 or 1) is used to multiply the value in the same row on that sheet, so when Potato is on a row, the formula is same as 1 * quantity, and if the word isn't Potato, it comes out as 0 * quantity. Each row's result is tracked internally and they are all added together to give a total for all rows with Potato in column D. If you wanted to include several different products in your total, you can simply add more instances of the SUMPRODUCT() within the total cell, as (all would be written on a single line in the cell just as with any worksheet formula)... = SUMPRODUCT(--(Order List!D2:D50=Potato),Order List!C2:C50) + SUMPRODUCT(--(Order List!D2:D50=Tomato),Order List!C2:C50) + SUMPRODUCT(--(Order List!D2:D50=Carrot),Order List!C2:C50) "boosbrde" wrote: Sorry, I am at work, and got interrupted when I posted this...just realized I forgot an important element, it should say: If any cell in column D on the Order List equals potatos add the number in the corresponding cell in column D here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. On Oct 12, 10:25 am, boosbrde wrote: Ok, Here's what I tried (one of various) =SUMPRODUCT(=if'Order List'! D2:D50=potatos),(D2:D50="Potato"),(C2:C50) I think I must have misunderstood the formula you are showing me, because it didn't work. It says the formula I typed contains an error. I have to admit, I'm not very experienced with formulas, just basic stuff. My mind tells me the formula should (more or less) say: If any cell in column D on the Order List equals potatos add it here in this cell in the summary sheet, and if it doesn't equal it, just ignore it. and, that I should be able to use the same formula (changing names of course) for the other products. The problem is, I really don't know how to write it in language excel will understand..... On Oct 12, 2:18 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Perhaps this 'hint' will give you a start, this assumes values in rows 1 through 4, change as required. It gives the total of all "Potato" entries in the range (word in D, quantity in C) =SUMPRODUCT(--(D1:D4="Potato"),C1:C4) there is almost always some help to be had at HelpFrom @ jlathamsite.com remove those spaces - if you really want help. " wrote: What I need is to be able to add the units of each product seperately in the Summary sheet, and get percentages. Essentially I need to be able to tell the Summary sheet the following: If any cell Column D in the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then add the number in Column C in the corresponding cell to the "running total". Note: Column D cells are a dropdown list (of various products) tied to the Summary page I have a sample spreadsheet I can show if it would help. ANY HELP would be GREATLY APPRECIATED.....as I have been working on this for over a week (off and on)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
IF statement(?) | Excel Discussion (Misc queries) | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |