Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a column based on input in a cell
Hi everyone - I hope someone can help me with this problem.
I have a simple table, with say Jan, Feb and March as headers in columns A, B and C. Under each one, I have a list of defined text values (High, Medium and Low). On a seperate sheet, I want to count the number of times High, Medium and Low occurs for each month. But the tricky thing is this. On the seperate sheet I want one cell where the user can select the month of interest (Jan, Feb and March) and then I want the results to change depending on the month selected. Any help much appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a column based on input in a cell
With headers in Row1, say your data is in A2 to C100 on Sheet1.
Select A1 to C1 and click in the name box (left of the formula bar), Key in "list" (no quotes), And hit <Enter. You have just assigned a name to your datalist headers. Let's now assign names to the individual data columns: Select the *entire* datalist, from A1 to C100, then, from the Menu Bar, <Insert <Name <Create And make sure that *only* "top row" is checked, then <OK. NOW, On Sheet2, in B1, C1 and D1, key in High, Medium and Low. Click in A2, and from the Menu Bar: <Data <Validation Under the "Settings" tab, expand the "Allow" box, and click on "List". Then, in the "Source" box, enter this: =list Then <OK. When A2 is selected, this gives you a drop down box with the 3 choices of the headers in your datalist. Now, in B2, enter: =IF($A2="","",COUNTIF(INDIRECT($A2),B1)) And copy across to D2. You will now display the various counts of whatever month is selected in A2. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Taza" wrote in message ... Hi everyone - I hope someone can help me with this problem. I have a simple table, with say Jan, Feb and March as headers in columns A, B and C. Under each one, I have a list of defined text values (High, Medium and Low). On a seperate sheet, I want to count the number of times High, Medium and Low occurs for each month. But the tricky thing is this. On the seperate sheet I want one cell where the user can select the month of interest (Jan, Feb and March) and then I want the results to change depending on the month selected. Any help much appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a column based on input in a cell
Try this...
Sheet1 A1:C1 = Jan, Feb, Mar Sheet1 A2:C10 = H, M, L H = High M = Medium L = Low Sheet2 A1 = drop down list containing the selections Jan, Feb, Mar How to create a drop down list: http://youtube.com/watch?v=t2OsWJijrOM Sheet2 A3:A4 = H, M, L Enter this formula in Sheet2 B2 and copy down to B4: =COUNTIF(INDEX(Sheet1!A$2:C$10,,MATCH(A$1,Sheet1!A $1:C$1,0)),A2) -- Biff Microsoft Excel MVP "Taza" wrote in message ... Hi everyone - I hope someone can help me with this problem. I have a simple table, with say Jan, Feb and March as headers in columns A, B and C. Under each one, I have a list of defined text values (High, Medium and Low). On a seperate sheet, I want to count the number of times High, Medium and Low occurs for each month. But the tricky thing is this. On the seperate sheet I want one cell where the user can select the month of interest (Jan, Feb and March) and then I want the results to change depending on the month selected. Any help much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
summing column c based an column a criteria | Excel Worksheet Functions | |||
summing numbers based on 2nd column | Excel Worksheet Functions | |||
Summing rows based on column headings | Excel Worksheet Functions | |||
Summing one column based on date in another column | New Users to Excel |