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 said:
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 said:
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 said:
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!
