MrExcel Publishing
Your One Stop for Excel Tips & Solutions

List ?


Posted by Patrick on February 25, 2001 6:16 AM


I a using the count if statement
=COUNTIF(List,"AM")
what I would like to do is complicated
beside the cell that has the formula countif is in
i have a drop down list with different choices for scheduling
AM
PM
Etc

what I would like is that when I drop down to make a choice
that the formula
=COUNTIF(List,"AM")
changes out also....
example
if i selected the PM from the drop down the formula
now is =COUNTIF(List,"PM")


Also I have defined a name and the cells the name refers to
I want to go across the work sheet....and then the range would carry
on to another row
I have this for the first row
='whatever'!$A$1:$H$8
however when I select another row
='whatever'!$A$1:$H$8,'whatever'!$A$5:$H$8
the countif that is refering to the
defined name shows value.....

thanks anyone
please


Posted by Aladin Akyurek on February 25, 2001 6:37 AM

Patrick,

I'm not sure but your first query seems to suggest that the condition/criteria argument should reflect the choice that is made via the dropdown list. If so, that is easy.

Assuming that the drop down list is in A1, you can change the COUNTIF formula to:

=COUNTIF(list, A1)

You can replace A1 by, say, choice, if you name A1 "choice" by using the Name Box or the option Insert, Define, Name while in A1.

Is this what you want?

And, care to elaborate your query involving changing "range" bit?

Aladin

Posted by Dave Hawley on February 25, 2001 6:59 AM


Hi Patrick

For your list choice, try this. Note all ranges are optional.

1. In cells A1:A5 put your list of choices. E.g. "AM","PM",etc.

2. Select cell B1. Go to Data>Validation and select "List" from the "Allow" box. put: =A1:A5 in the "Source" box. Set the Input Message and Eror Alert as needed. Click OK.

3. In any cell put =COUNTIF(List,B1)

Like Aladin I'm not sure what you mean by your second part of your question. But I'm thinking you want a Dynamic named range. If you go to me web site (OzGrid Business Applications) and follow the link "Dynamic Named Ranges" you may get what your after.

Any good ?

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on February 25, 2001 7:03 AM

I thought Patrick has already a dropdown list!

Aladin

Posted by Dave Hawley on February 25, 2001 8:15 AM

I thought Patrick has already a dropdown list! Aladin

Hmm, you could be right! Patrick is this true ? do you ?

Dave :o)

OzGrid Business Applications

Posted by Patrick on February 25, 2001 7:08 PM

Here is another shot at the second part
I have these formulas (below) in separate cells to
count "AM" in the rows as indicated in each named
list
How would I be able to get the same
sum of the "AM" writing only one formula

=COUNTIF(List,"AM")
='whatever'!$B$3:$H$3

=COUNTIF(List2,"AM")
='whatever'!$B$22:$H$22

=COUNTIF(List3,"AM")
='whatever'!$B$41:$H$41

=COUNTIF(List4,"AM")
='whatever'!$B$60:$H$60

If I am not clear may I send the file

Posted by Aladin Akyurek on February 25, 2001 7:28 PM

Patrick: Why not send the file? -Aladin

Posted by Aladin Akyurek on February 25, 2001 9:46 PM

Now that I saw your file, it's much easier to answer the question "How would I be able to get the same sum of the "AM" writing only one formula?".

You can just sum up the COUNTIFs applied to different ranges to get the total count of "AM"s:

=COUNTIF(List,"AM")+COUNTIF(List2,"AM")+COUNTIF(List3,"AM")

Or you can use an array formula:

=SUM((List="AM")+(List2="AM")+(List3="AM"))

You must enter the last one by hitting CONTROL+SHIFT+ENTER at the same time instead of hitting just ENTER.

Aladin


Posted by David Hawley on February 26, 2001 5:21 AM

: If I am not clear may I send the file : Patrick: Why not send the file? -Aladin Now that I saw your file, it's much easier to answer the question "How would I be able to get the same sum of the "AM" writing only one formula?". You can just sum up the COUNTIFs applied to different ranges to get the total count of "AM"s: =COUNTIF(List,"AM")+COUNTIF(List2,"AM")+COUNTIF(List3,"AM") Or you can use an array formula: =SUM((List="AM")+(List2="AM")+(List3="AM")) You must enter the last one by hitting CONTROL+SHIFT+ENTER at the same time instead of hitting just ENTER. Aladin

Hi Aladin and Patrick

I don't mean to but in here, but why not use:

=COUNTIF(List1:List3,"AM")


Dave


OzGrid Business Applications

Posted by Aladin Akyurek on February 26, 2001 5:42 AM

DAVE: The way Patrick's data is organized, you would get the wrong total count. There are other runs of AMs between separate ranges. Your formula will get them all, an unintended outcome. He actually needs total counts based on 2 or more criteria.

Aladin

Posted by David Hawley on February 26, 2001 7:15 AM


In that case, your right.

Dave
OzGrid Business Applications