Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count occurrences where 2 columns are required to be true?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count occurrences where 2 columns are required to be true?
Brandoni, here is one way,
=SUMPRODUCT((A1:A25="Tom")*(B1:B25="Job")) -- 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 "Brandoni" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using COUNTIF to search for existence | Excel Worksheet Functions | |||
How do I count values across multiple columns? | Excel Worksheet Functions | |||
counting in one column when two expressions in two other columns are true | Excel Worksheet Functions | |||
Nexsted if across 3 columns | Excel Worksheet Functions | |||
Count certain changes between columns | Excel Worksheet Functions |