# Thread: Excel: Count Alphanumeric Cells and total

1. I need to total fields within a roster. The shifts will vary and they will either have an "a", "A", "b" or "B". I need to total these amounts against the equivalent shift i.e. 8 (for an 0800 start), 1300 (for a 1300 start) etc.

I used to be able to use the count feature as I just used the numeric but now we are making up teams "A" and "a" and "B" and "b".

I have attached a copy of one roster page. Looked at the SUM array but it will only return criteria that matches all. DCOUNTA but not sure if this is what I am after. Can someone point me in the right direction?

2. In cell B60, you can use =COUNTIF(B\$4:B\$56,\$A60&"*")
Fill down, then right or right, then down.

3. Thanks HansV - something so simple. Was thinking too hard when an easy option was there. Very much appreciated. Jac

4. Hi Hans

Need to now add the total shifts by a particular doctor by the shift i.e. 7, 7a, 7A, 8, 1300 etc. I assume I need to use the 3D function as I have fortnightly rosters. I now have some shifts that are 7 or 7a. Do I use the Sum array on grouped sheets? I need to find out how many of each shift type each doctor has done to give me total. I can't use the Countif in an array.

5. [quote name='jacquir' post='764283' date='09-Mar-2009 06:54']Hi Hans

Need to now add the total shifts by a particular doctor by the shift i.e. 7, 7a, 7A, 8, 1300 etc. I assume I need to use the 3D function as I have fortnightly rosters. I now have some shifts that are 7 or 7a. Do I use the Sum array on grouped sheets? I need to find out how many of each shift type each doctor has done to give me total. I can't use the Countif in an array.[/quote]
See here for 3D functions that perform a Countif or Sumif over several sheets.

6. Thanks for link but way beyond me...Jacqui

7. [quote name='jacquir' post='764530' date='10-Mar-2009 06:38']Thanks for link but way beyond me...Jacqui[/quote]
You don't have to understand the code, you can simply copy it into a module, and use it in formulas.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•