How do I calculate text data in 2 different columns?
 

I have names "McCreight, Doug" in one column and their single letter job code
"M" in next column over. How do I calculate
1. how many rows have a name and a job code?
2. how many rows have entry "vacant" and a job code

Thanks

How do I calculate text data in 2 different columns?
 

Maybe something like these would suffice:
1. =SUMPRODUCT((OR(A2:A10<{"","vacant"}))*(B2:B10<" "))
2. =SUMPRODUCT((A2:A10="vacant")*(B2:B10<""))
assuming names/"vacant" in col A, job codes in col B
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dougmcr8" wrote:
I have names "McCreight, Doug" in one column and their single letter job code
"M" in next column over. How do I calculate
1. how many rows have a name and a job code?
2. how many rows have entry "vacant" and a job code

Thanks

How do I calculate text data in 2 different columns?
 

I will assume names in A1:A100, code in B1:B100
Has a name and a jobe code
=SUMPRODUCT(--(A1:A100""),--(B1:B100''") )
' that is two negatives in a row - and - ; and two double quotes " and
"
Has a name and code is M
=SUMPRODUCT(--(A1:A100""),--(B1:B100=''M"))

No name, but has code
=SUMPRODUCT(--(ISBLANK(A1:A100)),--(NOT(ISBLANK(B1:B100))))

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"dougmcr8" wrote in message
...
I have names "McCreight, Doug" in one column and their single letter job
code
"M" in next column over. How do I calculate
1. how many rows have a name and a job code?
2. how many rows have entry "vacant" and a job code

Thanks