How can I count based on 2 values?
 

I have a sheet with 2 columns. One column the user enters one of three
possible values in the first column. There is a piece of code in the
worksheet that based on their selection the second column will have a
validation list, relating to what they have entered in column 1.

What I want to do is count the number of times each option comes up in
column 1. That part is fine, I simply use the COUNTIF command. But one of
the possible entries is the word "Other". I only want to count the "Other"
entries if it's corresponding entry in column 2 dose not equal "Journey Home"

Can this be done?

How can I count based on 2 values?
 

=SUMPRODUCT(--(A2:A20="Other"),--(B2:B20="Journey Home")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Keith" wrote in message
...
I have a sheet with 2 columns. One column the user enters one of three
possible values in the first column. There is a piece of code in the
worksheet that based on their selection the second column will have a
validation list, relating to what they have entered in column 1.

What I want to do is count the number of times each option comes up in
column 1. That part is fine, I simply use the COUNTIF command. But one of
the possible entries is the word "Other". I only want to count the "Other"
entries if it's corresponding entry in column 2 dose not equal "Journey
Home"

Can this be done?

How can I count based on 2 values?
 

" column 2 dose not equal "Journey Home"---
col 1 = col A
col 2 = col B

I suggest you start Now to explore the SUM array formula....

=SUM((a2:a100="Other")*(b2:b100<"Journey Home"))
on edit mode, press control-shift-enter
excel will validate the array formula.
it will look like this..
{=SUM((a2:a100="Other")*(b2:b100<"Journey Home"))}

mine driller


"Keith" wrote:

I have a sheet with 2 columns. One column the user enters one of three
possible values in the first column. There is a piece of code in the
worksheet that based on their selection the second column will have a
validation list, relating to what they have entered in column 1.

What I want to do is count the number of times each option comes up in
column 1. That part is fine, I simply use the COUNTIF command. But one of
the possible entries is the word "Other". I only want to count the "Other"
entries if it's corresponding entry in column 2 dose not equal "Journey Home"

Can this be done?

How can I count based on 2 values?
 

Thanks for that it works a treat. Now I just need to work out how it works.

By the way, you forgot the second closing bracket at the end of the command.
:-)

Thanks again

Keith

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20="Other"),--(B2:B20="Journey Home")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Keith" wrote in message
...
I have a sheet with 2 columns. One column the user enters one of three
possible values in the first column. There is a piece of code in the
worksheet that based on their selection the second column will have a
validation list, relating to what they have entered in column 1.

What I want to do is count the number of times each option comes up in
column 1. That part is fine, I simply use the COUNTIF command. But one of
the possible entries is the word "Other". I only want to count the "Other"
entries if it's corresponding entry in column 2 dose not equal "Journey
Home"

Can this be done?

How can I count based on 2 values?
 

So I did. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a
detailed explanation.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Keith" wrote in message
...
Thanks for that it works a treat. Now I just need to work out how it
works.

By the way, you forgot the second closing bracket at the end of the
command.
:-)

Thanks again

Keith

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20="Other"),--(B2:B20="Journey Home")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Keith" wrote in message
...
I have a sheet with 2 columns. One column the user enters one of three
possible values in the first column. There is a piece of code in the
worksheet that based on their selection the second column will have a
validation list, relating to what they have entered in column 1.

What I want to do is count the number of times each option comes up in
column 1. That part is fine, I simply use the COUNTIF command. But
one of
the possible entries is the word "Other". I only want to count the
"Other"
entries if it's corresponding entry in column 2 dose not equal
"Journey
Home"

Can this be done?