Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old July 21st 09, 04:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 10
Default Count If Column A = x and Column B = y

Well...one correction, when I let the computer pick, it did not include the *
so I had to go back and fix it, but for the first time, I saw it work inter
sheet. Maybe now I can do this with the other file. Talk to you in another
3 months.

"Fred Smith" wrote:

All you need to do is specify your ranges properly.
Instead of:
=SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B")
Use:
=SUMPRODUCT(Sheet1!$A$2:$A$20="B")

Note in this formula, you specified the range properly the first time, but
not the second time:
=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1))
Use this instead:
=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:B $20=1))

The best way to insert ranges into a formula is to get Excel to do it.
Contrary to humans, Excel will specify the range properly every time. When
writing a formula, do the following (using your Sumproduct as an example):
Enter:
=sumproduct((
Now highlight the range you want to use. Go to Sheet2, and highlight cells
a2:a20. Watch the navigation bar, and you will see Excel insert the proper
range addresses. If you want an absolute address, press F4.
Continue entering the formula you want, as in:
="B")*(
Highlight your second range
Finish off the formula:
))

Regards,
Fred

"Altair1972m" wrote in message
...
Fred I found the problem. It does not seem to work between worksheets.

In sheet one I made 3 simple columns:

B 2 X
B 2 X
A 1 Y
B 1 Y
B 1 X
B 2 X
B 2 X

I ran the basic subproduct

=+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X"))

and got - 1

Then I inserted a new sheet

I put in

=SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B")

Answer - 0

=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1))
=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X"))
Answer - #VALUE

I stopped there and copied this for you.

So...does sumproduct work across pages?

If not, do not feel like this was a complete waste of time, because I can
still eliminate most of my concatinations.


"Fred Smith" wrote:

We've sure been beating around the bush if all you want to do is sum a
column. Solutions to this problem are easily found.

What column do you want to sum? What are the conditions that need to be
met?
What version of Excel do you have?

Regards,
Fred

"Altair1972m" wrote in message
...
Fred, I've been playing with this on an off for months and I think I
see
my
confusion:

I was trying to sum the contents of a column. Was this command you
were
instructing designed to sum the column or PROVIDE ME with the judgement
call
on whether this column should be part of the summation?

"Fred Smith" wrote:

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.


  #22   Report Post  
Old July 21st 09, 04:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2,389
Default Count If Column A = x and Column B = y

That's right, Excel will not enter operators. You have to do that yourself.
You will see that my instructions stated you needed to enter the * (and the
parentheses) yourself.

Regards,
Fred

"Altair1972m" wrote in message
...
Well...one correction, when I let the computer pick, it did not include
the *
so I had to go back and fix it, but for the first time, I saw it work
inter
sheet. Maybe now I can do this with the other file. Talk to you in
another
3 months.

"Fred Smith" wrote:

All you need to do is specify your ranges properly.
Instead of:
=SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B")
Use:
=SUMPRODUCT(Sheet1!$A$2:$A$20="B")

Note in this formula, you specified the range properly the first time,
but
not the second time:
=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1))
Use this instead:
=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:B $20=1))

The best way to insert ranges into a formula is to get Excel to do it.
Contrary to humans, Excel will specify the range properly every time.
When
writing a formula, do the following (using your Sumproduct as an
example):
Enter:
=sumproduct((
Now highlight the range you want to use. Go to Sheet2, and highlight
cells
a2:a20. Watch the navigation bar, and you will see Excel insert the
proper
range addresses. If you want an absolute address, press F4.
Continue entering the formula you want, as in:
="B")*(
Highlight your second range
Finish off the formula:
))

Regards,
Fred

"Altair1972m" wrote in message
...
Fred I found the problem. It does not seem to work between worksheets.

In sheet one I made 3 simple columns:

B 2 X
B 2 X
A 1 Y
B 1 Y
B 1 X
B 2 X
B 2 X

I ran the basic subproduct

=+SUMPRODUCT(($A1:$A20="B")*($B1:$B20=1)*($C1:$C20 ="X"))

and got - 1

Then I inserted a new sheet

I put in

=SUMPRODUCT(Sheet1!$A$2:Sheet1!$A$20="B")

Answer - 0

=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$B$2:S heet2!B$20=1))
=SUMPRODUCT((Sheet1!$A$2:$A$20="B")*(Sheet1!$C$2:S heet2!C$20="X"))
Answer - #VALUE

I stopped there and copied this for you.

So...does sumproduct work across pages?

If not, do not feel like this was a complete waste of time, because I
can
still eliminate most of my concatinations.


"Fred Smith" wrote:

We've sure been beating around the bush if all you want to do is sum a
column. Solutions to this problem are easily found.

What column do you want to sum? What are the conditions that need to
be
met?
What version of Excel do you have?

Regards,
Fred

"Altair1972m" wrote in message
...
Fred, I've been playing with this on an off for months and I think I
see
my
confusion:

I was trying to sum the contents of a column. Was this command you
were
instructing designed to sum the column or PROVIDE ME with the
judgement
call
on whether this column should be part of the summation?

"Fred Smith" wrote:

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.


  #23   Report Post  
Old February 2nd 19, 12:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2019
Posts: 1
Default Count If Column A = x and Column B = y

On Tuesday, April 21, 2009 at 10:39:46 AM UTC+10, Chin via OfficeKB.com wrote:
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


Hi Chin - this works great for two fields, but not for three do you know a workaround for this? Ie I want to count how many letters (Column A), sent to person (Column B), responded to Y or N (Columnn C) Thx


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Based on Mulipple Criteria acrossed Column count last column LBitler Excel Worksheet Functions 1 February 12th 09 06:58 PM
Count cells w/values in column if the data in column a matches cri mdcgpw Excel Worksheet Functions 4 January 12th 09 11:55 PM
Count number of cells and total in one column, based on another column suffix Pierre Excel Worksheet Functions 5 October 31st 07 12:28 AM
Count entries in one column based on values in another column Kurt Excel Worksheet Functions 7 January 10th 07 09:29 PM
SUM/COUNT column(s) based on specific value present within the column markx Excel Worksheet Functions 6 March 22nd 05 10:23 AM


All times are GMT +1. The time now is 09:34 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017