List ?

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

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

I thought Patrick has already a dropdown list!

Aladin

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

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

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

: 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

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

In that case, your right.

Dave
OzGrid Business Applications