COUNTIFS Function
 

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!

COUNTIFS Function
 

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" wrote in message
...
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!

COUNTIFS Function
 

Try this:

=SUM(COUNTIF('[Member
List(1).xls]Sheet1'!$C$2:$C$1108,{"azerbaijan,"armenia"}))


"MOLLY66" wrote:

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!

COUNTIFS Function
 

Hi "Fred"

Thanks so much, I've been wrestling with this problem for a while. You've
saved me so much time, not to mention frustration!

Regards
Molly

"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" wrote in message
...
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!

COUNTIFS Function
 

Thanks for your reply, I tried the option supplied by Fred Smith first of all
which works fine. Cheers!

"Teethless mama" wrote:

Try this:

=SUM(COUNTIF('[Member
List(1).xls]Sheet1'!$C$2:$C$1108,{"azerbaijan,"armenia"}))


"MOLLY66" wrote:

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!

COUNTIFS Function
 

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" wrote in message
...
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!

COUNTIFS Function
 

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" wrote in message
...
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" wrote in message
...
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!

COUNTIFS Function
 

Hi Fred

It does help, thanks very much. You're a very good tutor!

Regards
Molly


"Fred Smith" wrote:

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" wrote in message
...
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" wrote in message
...
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!