No problem, Molly. What I do is get Excel to put the range address for me.
I'll use a single Countif as an example, but you can extend this to cover
any function or formula.
1. Enter =Countif( into the cell where you want the answer.
2. Now navigate to the range you want to count. You can use the arrow keys
or the mouse. Typically the mouse is easier. Just highlight the range (in
any open sheet or book).
3. Now enter the comma (,).
4. You will see in the formula bar that Excel has filled in the range for
you.
5. Finish off the function (eg, "armenia"))
6. Hit enter.
Now you have the correct formula in your cell. I find this much easier than
entering addresses myself because I always get them wrong. If you want
absolute addresses (like the ones in your original formula, hit F4 to get
the $ signs inserted).
However, it's also useful to know how Excel handles addresses, so they can
be changed later if needed. Here is the Countif from the original formula:
=COUNTIF('[Member List(1).xls]Sheet1'!$C$2:$C$1108,"azerbaijan")
In this case the workbook (file) name is "Member List(1).xls". If you want
to change the name, change this part.
The sheet within the workbook is "Sheet1".
The range is C2:C1108. The dollar signs signify an absolute address (one
that won't be changed when it's copied).
You need square brackets around a workbook name, and exclamationpoint
between the sheet name and the range, and single quotes if the names contain
a space.
Hope this helps,
Fred.
"Molly66" <(E-Mail Removed)> wrote in message
news:6D4AF0DD-E388-41D9-B71E-(E-Mail Removed)...
> Hi Fred
>
> I'm sorry if this isn't the correct etiquette, I'm a new user so I hope
> you
> don't mind me asking you again. I had a further question and posted it
> but
> I'm getting tied in knots with the replies, so I hoped you could help as
> it's
> a stage further than my last one.
>
> Your suggestion worked like a charm but I would now like to be able to
> have
> the countif function count the data from a different range of cells in a
> different spreadsheet. I don't know how to keep the countif function
> suggested by you in my main spreadsheet but change the source spreadsheet
> and
> the range of cells. I did try edit links on the Data tab and chose a
> different spreadsheet but don't know how to change the range.
>
> Can you help, please?
>
> Regards
> Molly
> --
> Thanks in advance
>
>
> "Fred Smith" wrote:
>
>> Your problem is that Countifs is looking for both criteria to be true.
>> This,
>> of course, is impossible. You can't be both Armenia and Azerbaijan at the
>> same time.
>>
>> Forget Countifs. Just use good old-fashioned Countif, as in:
>>
>> =COUNTIF('[Member
>> List(1).xls]Sheet1'!$C$2:$C$1108,"azerbaijan")+COUNTIF('[Member
>> List(1).xls]Sheet1'!$C$2:$C$1108,"armenia")
>>
>> Regards,
>> Fred.
>>
>> "MOLLY66" <(E-Mail Removed)> wrote in message
>> news:5572E6CC-83AB-4C3C-833C-(E-Mail Removed)...
>> >I have a spreadsheet with countries in one column. I am trying to count
>> >the
>> > number of times Armenia and Azerbaijan appear in this one column as a
>> > total.
>> > I am using this function =COUNTIFS('[Member
>> > List(1).xls]Sheet1'!$C$2:$C$1108,"azerbaijan",'[Member
>> > List(1).xls]Sheet1'!$C$2:$C$1108,"armenia") where Member List is the
>> > name
>> > of
>> > my spreadsheet and the range I want to counted is C2:C1108. Cans
>> > omeone
>> > please tell me where I'm going wrong? Much appreciated!
>>
>>
|
Similar Threads
