Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2006

Tip: Looking for answers? Try searching our database.

How to count occurrences where 2 columns are required to be true?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brandoni - 04 Nov 2006 13:53 GMT
I am trying to count the number of occurences when conditions in two columns
are met.  I can count each individual column values with =countif function.  
But, I can't seem to count only if a value is found in both column A and B to
be true.  Please see example below.

A1= Tom    B1=Job
A2= John    B2=Job
A3= John    B3=Spec
A4= Mark    B4=Spec
A5= Alice    B5=Spec
A6= Tom    B6=Spec

What I would like to count is the number of "Jobs" each person has versus
"Spec" jobs.

For example:  If I count the number of jobs Tom is working on = 2
(COUNTIF(A:A,"Tom").  But I also want to return the number of Spec jobs only
Tom is working on = 1 (This is the formula I need.  Basically, count each row
that has Tom and Spec as TRUE).

Thank you in advance for your help.  Please email copy your response.
Paul B - 04 Nov 2006 14:26 GMT
Brandoni, here is one way,

=SUMPRODUCT((A1:A25="Tom")*(B1:B25="Job"))

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> I am trying to count the number of occurences when conditions in two columns
> are met.  I can count each individual column values with =countif function.
[quoted text clipped - 17 lines]
>
> Thank you in advance for your help.  Please email copy your response.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.