How do I calculate text data in 2 different columns?

  • Thread starter Thread starter dougmcr8
  • Start date Start date
D

dougmcr8

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
 
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
 
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
 
Back
Top