Summing a column based on input in a cell

[Taza]

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.

 

[Ragdyer]

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.

 

[T. Valko]

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:

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)