Count If Column A = x and Column B = y

I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to look in
the first column to see if it's A, then look in 2nd column to see if it's B,
then count it... is that possible?

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to look in
the first column to see if it's A, then look in 2nd column to see if it's B,
then count it... is that possible?

"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to look in
the first column to see if it's A, then look in 2nd column to see if it's B,
then count it... is that possible?


I had this same question earlier in the year. I just tried to plug in the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a month from a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main spreadsheet.
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets working
off the data of where.



In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)).

And that totaled the number of people who came in on that date in that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.

Trish, please try:

=COUNTIFS(A1:A100,"A",B1:B100,"B")

Please advise if that is what you were looking for.


Trish wrote:
I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to look in
the first column to see if it's A, then look in 2nd column to see if it's B,
then count it... is that possible?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200904/1

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very useful
function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.
=S3&YEAR(A3)&MONTH(A3)
works just as well, and will be easier for others to understand.

Regards,
Fred.

"Altair1972m" wrote in message
...


"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to
look in
the first column to see if it's A, then look in 2nd column to see if
it's B,
then count it... is that possible?


I had this same question earlier in the year. I just tried to plug in the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a month from
a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main
spreadsheet.
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets working
off the data of where.



In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)).

And that totaled the number of people who came in on that date in that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.

This works only in Excel 2007. You should identify this when you are using
functions found only in the newest version.

Regards,
Fred.

"Chin via OfficeKB.com" u50624@uwe wrote in message
news:94e93ddbaa04b@uwe...
Trish, please try:

=COUNTIFS(A1:A100,"A",B1:B100,"B")

Please advise if that is what you were looking for.


Trish wrote:
I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to look
in
the first column to see if it's A, then look in 2nd column to see if it's
B,
then count it... is that possible?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200904/1

I think I found the problem: Can Sumproduct compare cell content to another
cell content?


I am so game for this, but I have been trying to play with the command on
and off for 2 days (on work time) and I'm not getting anywhere. I feel so dee
dee deeeeee here.

I’m trying
Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)

Client List Column A is the dates. Client List Column B is the volags.
Client List C is the County, Client List I is the Status as refugee.

On the Totals sheet the dates run across Row 2. Totals of the year are the
next row using the sum command, and then the next 12 rows list the monthy
totals with the criteria in A, B, and C

The formula above is In Cell E4 which is under the 2008-01 date, the USCC
VOLAG in Atlantic County

Here is the total table

2008-01
0
USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)
USCC CAMDEN 0
USCC ESSEX 0
USCC PASSAIC 0
USCC MERCER 0
LIRS ESSEX 0
LIRS MERCER 0
JFS BERGEN 0
JVS ESSEX 0
JFVS MIDDLESEX 0
IRC UNION 0
IRSA HUDSON 0


I put it in and got an error.

I assumed I had a problem with the date, so I tried the formula again this
time without the reference to clientlist A

Still have an error.

I think I found the problem: Can Sumproduct compare to a cell content?

So then I tried this:

+Sumproduct((‘Client List’!B$2:B$60000,="USCC")(‘Client
List’!C$2:C$60000,="ATLANTIC")( ‘Client List’!I$2:I$60000,=�R�))

Just to see if that was the problem. I still had an error.

Help please!


"Sometimes the genius and the idiot are the same person"
--Nitchie

"Fred Smith" wrote:

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very useful
function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.
=S3&YEAR(A3)&MONTH(A3)
works just as well, and will be easier for others to understand.

Regards,
Fred.

"Altair1972m" wrote in message
...


"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to
look in
the first column to see if it's A, then look in 2nd column to see if
it's B,
then count it... is that possible?


I had this same question earlier in the year. I just tried to plug in the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a month from
a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main
spreadsheet.
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets working
off the data of where.



In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)).

And that totaled the number of people who came in on that date in that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.

You are missing the arithmetic operator, and have extraneous commas. Try the
following:

Sumproduct(('Client List'!A$2:A$60000=E$2)*(‘Client List’!B$2:B$60000=$A4)*(‘Client
List’!C$2:C$60000=$B4)*(‘Client List’!I$2:I$60000=�R�))

Regards,
Fred

"Altair1972m" wrote in message
...
I think I found the problem: Can Sumproduct compare cell content to another
cell content?


I am so game for this, but I have been trying to play with the command on
and off for 2 days (on work time) and I'm not getting anywhere. I feel so
dee
dee deeeeee here.

I’m trying
Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)

Client List Column A is the dates. Client List Column B is the volags.
Client List C is the County, Client List I is the Status as refugee.

On the Totals sheet the dates run across Row 2. Totals of the year are
the
next row using the sum command, and then the next 12 rows list the monthy
totals with the criteria in A, B, and C

The formula above is In Cell E4 which is under the 2008-01 date, the USCC
VOLAG in Atlantic County

Here is the total table

2008-01
0
USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)
USCC CAMDEN 0
USCC ESSEX 0
USCC PASSAIC 0
USCC MERCER 0
LIRS ESSEX 0
LIRS MERCER 0
JFS BERGEN 0
JVS ESSEX 0
JFVS MIDDLESEX 0
IRC UNION 0
IRSA HUDSON 0


I put it in and got an error.

I assumed I had a problem with the date, so I tried the formula again this
time without the reference to clientlist A

Still have an error.

I think I found the problem: Can Sumproduct compare to a cell content?

So then I tried this:

+Sumproduct((‘Client List’!B$2:B$60000,="USCC")(‘Client
List’!C$2:C$60000,="ATLANTIC")( ‘Client List’!I$2:I$60000,=�R�))

Just to see if that was the problem. I still had an error.

Help please!


"Sometimes the genius and the idiot are the same person"
--Nitchie

"Fred Smith" wrote:

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very useful
function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.
=S3&YEAR(A3)&MONTH(A3)
works just as well, and will be easier for others to understand.

Regards,
Fred.

"Altair1972m" wrote in message
...


"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in
different
columns need to be satisfied and having no luck. Basically I want to
look in
the first column to see if it's A, then look in 2nd column to see if
it's B,
then count it... is that possible?


I had this same question earlier in the year. I just tried to plug in
the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my
work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a month
from
a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main
spreadsheet.
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets
working
off the data of where.



In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)).

And that totaled the number of people who came in on that date in that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.

Fred I SO appreciate the effort.

I'm just not used to being this success impaired.

I cut and pasted your formula in and then added the + at the beginning. It
still says there is a problem with the first array. And I even tried
changing 'Client List' to 'Client_List' but that didn't work.

"Fred Smith" wrote:

You are missing the arithmetic operator, and have extraneous commas. Try the
following:

Sumproduct(('Client List'!A$2:A$60000=E$2)*(‘Client List’!B$2:B$60000=$A4)*(‘Client
List’!C$2:C$60000=$B4)*(‘Client List’!I$2:I$60000=�R�))

Regards,
Fred

"Altair1972m" wrote in message
...
I think I found the problem: Can Sumproduct compare cell content to another
cell content?


I am so game for this, but I have been trying to play with the command on
and off for 2 days (on work time) and I'm not getting anywhere. I feel so
dee
dee deeeeee here.

I’m trying
Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)

Client List Column A is the dates. Client List Column B is the volags.
Client List C is the County, Client List I is the Status as refugee.

On the Totals sheet the dates run across Row 2. Totals of the year are
the
next row using the sum command, and then the next 12 rows list the monthy
totals with the criteria in A, B, and C

The formula above is In Cell E4 which is under the 2008-01 date, the USCC
VOLAG in Atlantic County

Here is the total table

2008-01
0
USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)
USCC CAMDEN 0
USCC ESSEX 0
USCC PASSAIC 0
USCC MERCER 0
LIRS ESSEX 0
LIRS MERCER 0
JFS BERGEN 0
JVS ESSEX 0
JFVS MIDDLESEX 0
IRC UNION 0
IRSA HUDSON 0


I put it in and got an error.

I assumed I had a problem with the date, so I tried the formula again this
time without the reference to clientlist A

Still have an error.

I think I found the problem: Can Sumproduct compare to a cell content?

So then I tried this:

+Sumproduct((‘Client List’!B$2:B$60000,="USCC")(‘Client
List’!C$2:C$60000,="ATLANTIC")( ‘Client List’!I$2:I$60000,=�R�))

Just to see if that was the problem. I still had an error.

Help please!


"Sometimes the genius and the idiot are the same person"
--Nitchie

"Fred Smith" wrote:

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very useful
function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.
=S3&YEAR(A3)&MONTH(A3)
works just as well, and will be easier for others to understand.

Regards,
Fred.

"Altair1972m" wrote in message
...


"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in
different
columns need to be satisfied and having no luck. Basically I want to
look in
the first column to see if it's A, then look in 2nd column to see if
it's B,
then count it... is that possible?


I had this same question earlier in the year. I just tried to plug in
the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my
work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a month
from
a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main
spreadsheet.
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets
working
off the data of where.



In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)).

And that totaled the number of people who came in on that date in that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.

1. To determine whether to use 'Client List' or 'Client_List', look at the
name of the spreadsheet on the tabs at the bottom. The name you use in your
formulas must match the name on the tab of the spreadsheet.

2. I had trouble with the copy and paste as well. When I created the
formula, I simply modified the one you has posted. However, when I pasted
that into Excel, it complained about the quotes. They weren't regular
quotes, but smart quotes, which Excel doesn't like. When I changed the
quotes, Excel accepted the formula. Here's a copy of what I used:
=SUMPRODUCT(('Client List'!A$2:A$60000=E$2)*('Client
List'!B$2:B$60000=$A4)*('Client List'!C$2:C$60000=$B4)*('Client
List'!I$2:I$60000="R"))
Remember, if this appears on more than one line in your message, you will
need to delete the word wrap.

Regards,
Fred.


"Altair1972m" wrote in message
...
Fred I SO appreciate the effort.

I'm just not used to being this success impaired.

I cut and pasted your formula in and then added the + at the beginning.
It
still says there is a problem with the first array. And I even tried
changing 'Client List' to 'Client_List' but that didn't work.

"Fred Smith" wrote:

You are missing the arithmetic operator, and have extraneous commas. Try
the
following:

Sumproduct(('Client List'!A$2:A$60000=E$2)*(‘Client List’!B$2:B$60000=$A4)*(‘Client
List’!C$2:C$60000=$B4)*(‘Client List’!I$2:I$60000=�R�))

Regards,
Fred

"Altair1972m" wrote in message
...
I think I found the problem: Can Sumproduct compare cell content to
another
cell content?


I am so game for this, but I have been trying to play with the command
on
and off for 2 days (on work time) and I'm not getting anywhere. I feel
so
dee
dee deeeeee here.

I’m trying
Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)

Client List Column A is the dates. Client List Column B is the volags.
Client List C is the County, Client List I is the Status as refugee.

On the Totals sheet the dates run across Row 2. Totals of the year are
the
next row using the sum command, and then the next 12 rows list the
monthy
totals with the criteria in A, B, and C

The formula above is In Cell E4 which is under the 2008-01 date, the
USCC
VOLAG in Atlantic County

Here is the total table

2008-01
0
USCC ATLANTIC Sumproduct(('Client List'!A$2:A$60000,=E$2)(‘Client
List’!B$2:B$60000,=$A4)(‘Client List’!C$2:C$60000,=$B4)( ‘Client
List’!I$2:I$60000,=�R�)
USCC CAMDEN 0
USCC ESSEX 0
USCC PASSAIC 0
USCC MERCER 0
LIRS ESSEX 0
LIRS MERCER 0
JFS BERGEN 0
JVS ESSEX 0
JFVS MIDDLESEX 0
IRC UNION 0
IRSA HUDSON 0


I put it in and got an error.

I assumed I had a problem with the date, so I tried the formula again
this
time without the reference to clientlist A

Still have an error.

I think I found the problem: Can Sumproduct compare to a cell content?

So then I tried this:

+Sumproduct((‘Client List’!B$2:B$60000,="USCC")(‘Client
List’!C$2:C$60000,="ATLANTIC")( ‘Client List’!I$2:I$60000,=�R�))

Just to see if that was the problem. I still had an error.

Help please!


"Sometimes the genius and the idiot are the same person"
--Nitchie

"Fred Smith" wrote:

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very useful
function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.
=S3&YEAR(A3)&MONTH(A3)
works just as well, and will be easier for others to understand.

Regards,
Fred.

"Altair1972m" wrote in message
...


"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))


"Trish" wrote:

I am trying to use the count function where two conditions in
different
columns need to be satisfied and having no luck. Basically I want
to
look in
the first column to see if it's A, then look in 2nd column to see
if
it's B,
then count it... is that possible?


I had this same question earlier in the year. I just tried to plug
in
the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my
work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a
month
from
a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main
spreadsheet.
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets
working
off the data of where.



In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!$U:$U,$A4&$B4&+YEAR(E$2)&+MONTH(E$2)).

And that totaled the number of people who came in on that date in
that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.