How can I count based on 2 values?

K

Keith

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?
 
B

Bob Phillips

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

--

HTH

Bob Phillips

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

driller

" 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
 
K

Keith

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
 
Top