Countif multiple criteria problem using text and numbers

[jmendenhall1960]

OK what I have is a list of names in this format: Mendenhall, John LPN, in A2 thru A70. In cell B2:B70 I have numbers with and without a letter example: 6p, 6d, 12p, 12d, D, ML, AL etc and the word "noon". What I want to do is countif the last letter in the name column A2:A70 has an "N" and if the number (always first digit) in the B column is >1 and countif A2:A70 has an "N" and B2:B70 "noon".
It really seems straight forward to me but for the life of me I can't figure out how to get excel to look at last and first digit in cell using the countif for two criteria.
Of course thank you for looking and any help.

 

[DILIPandey]

Not clear enough.

A3 is having N as letter
B3's first letter 6 is >1
now what ?
Do you want to count how many "N" in column A and how many "noon" in column B but limited to row 3 only because 3rd row met the criteria ?


Regards,
DILIPandey

 

[Peter_SSs]

Providing a smallish set of sample data, expected results and explanation of why those results should help clarify your requirement.

 

[jmendenhall1960]

The followiny is a sample of what I have on my sheet with an example of results.
Raey, Cheryl LPN 6P 12P 12P
Rhid, Sandy LPN D D 12D
Bner, Denise DA 12P 12P 12P
Jnson, Darrell DA 7D 7D 7D
Sman, Doug DA 12D D D

Hris, Ed RN 7D 4P noon
Bch, Connie LPN noon 6D 7D
Ctrell, Boyd LPN ML 7P 7D
Fnish, Ray LPN D 4D 4D
Bts, Ron DA D 7D 7D

Burns, shua RN 12D 4D 4P
Parrott, Fecia RN D 7D 7P
Meenhall, John LPN 4D D 4D
Loudeack, Rob DA 4P D D
Stepns, Willa DA D 4P 4P

For Night shift; If RN or LPN and any of the following: 12p or 12d then count 1 for each Nurse and put total in cell. Exp. First column “1” 2^nd column “1” 3^rd column “2”
For day shift; If RN or LPN and any of the following: 6p, 6d, noon, 7d, 7p then count 1 for each Nurse and put total in cell. Exp. First column “2” 2^nd column “2” 3^rd column “4”
For 2nd shift; If RN or LPN and any of the following: noon (count for day and 2nd shifts), 4d, 4p then count 1 for each Nurse and put total in cell. Exp. First column “3” 2^nd column “3” 3^rd column “4”.
I hope this is a better exp. and description.
Thank you again for looking.

 

[DILIPandey]

Still not clear to me

I guess if you put headings on top of the data and directly copy paste from Excel, your data would look like below example and would be easy to understand.

heading 1	heading 2	heading 3	heading 4	heading 5
6	8	9	1	3
5	1	5	5	9
1	7	2	5	8
7	6	6	6	8
8	9	3	1	4

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Regards,
DILIPandey

 

[Peter_SSs]

Unclear to me too.

You are now referring to "night shift", "day shift" and "2nd shift" with no indication in your data or explanation of which data relates to each of those shifts.

Your first post talked about the first digit in column B being > 1 but your last post makes no mention of that.

We really do need to see a better example of layout, including row/column labels and including the results layout as well.

My signature block below has suggestions for how to post small screen shots and where to test them.

 

[jmendenhall1960]

Thank you for the tips, In a nut shell all I am trying to do is count the number of LPN/RN (nurses) for each shift and place in a cell. Days encompasses 6p, 6d, 7d, 7p, noon. 2nd shift = noon (I know it already counted but I need it counted here also), 4p, 4d and nights = 12p and 12d. So this is what I mean by two criteria needs to be met first column A (Name) must have LPN or RN and column B (date) must have one of the other variables for shift..

Deadline for next schedule COB4/18/14	5/18	5/19	5/20
S	M	T
Raiy, Cheryl LPN	6P	12P	12P
Rach, Sandy LPN	D	D	12D
Brer, Denise DA	12P	12P	12P
Johnn, Darrell DA	7D	7D	7D
Seam, Doug DA	12D	D	D
Hais, Ed RN	7D	4P	D
Bnch, Connie LPN	D	6D	7D
Carell, Boyd LPN	ML	7P	7D
Fuish, Ray LPN	D	4D	4D
Bets, Ron DA	D	7D	7D
Bns, Joshua RN	4D	4D	4P
Paott, Felicia RN	D	7D	7P
Menhall, John LPN	4D	D	4D
Loerback, Rob DA	4P	D	D
Sthens, Willa DA	D	4P	4P
End

<tbody>
</tbody><colgroup><col><col span="3"></colgroup>

 

[DILIPandey]

Thanks for the sample data.
Also show some expected answers - manually entered along with brief logic.



Regards,
DILIPandey

 

[Peter_SSs]

Also, you are still talking about different shifts, but no indication in your sample data about what represents the different shifts.

 

[jmendenhall1960]

OK for 5/18 the results should be, for days formula in Cell A20 "2" because A3 has LPN and B3 has 6p (+1) and A8 has RN and B8 has7D (+1) and the criteria for Days encompasses LPN/RN in A3:A17 and 6p, 6d, 7d, 7p, noon in corresponding B when A has LPN/RN. for 5/19 results would be “3” Connie, Boyd and Felicia

For 2^nd shift Cell A21 it should result “2” because A14 has RN and B14 has 4D (+1) then A16 has RN and B16 has 4d (+1) and the criteria is: noon, 4P and 4D. 5/19 would be “3” Ed, Ray, and Joshua meet the two criteria.

For nights Cell A22 results should be “0”because no LPN/RN in A3:A17 which meets the two criteria LPN/RN and 12D or 12P. for 5/19 “2” Cheryl and Sandy meet both criteria.

Also If possible I would like to keep original sheet the same as there are alot of other formulas working in this sheet.
Thank you again.