Solved

Excel 2010 Countif problem

Posted on 2014-12-19
9
141 Views
Last Modified: 2014-12-24
I can’t get my spreadsheet to count up the number of Z jobs.  I tried several different variations of the formula, but if there is more than 1 job in a cell, it only counts it once.  I can’t figure out how to get Excel to count all the Z jobs.
ZZZ-Job.xlsx
0
Comment
Question by:WalterAPO
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40509352
As long as the Z can't appear anywhere else in the cell contents, you can use:
=SUM((INDEX(LEN(A2:A111)-LEN(SUBSTITUTE(A2:A111,"Z","")),)))
0
 
LVL 1

Expert Comment

by:neel_7575
ID: 40509440
Do a Find - Replace All - Look for "Z", Replace with "A" or something - Excel does the Replace and gives the no. of instances replaced. Then just do an Undo, to get back the original cells
0
 

Author Comment

by:WalterAPO
ID: 40509450
The above formula is a little complicated.  I need a formual, (replace all won't work), that I can show my coworkers.  If it's too complicated they wont' use it.  Can I add a - or / between the Z job numbers to get them to count?  For this formual simple is better.   Thank you.  

Walter
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Expert Comment

by:neel_7575
ID: 40509465
Your existing formula can still work - Just do a Text to Columns - to have the Z Jobs in individual cells - Delimiter being Comma
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40509476
If you want a simple formula you need a simple data layout.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40509496
In your sample, all jobs within each cell start with Z. Will this always be the case?

If so, this simple formula in column C will tell you how many jobs are in the cell in column A:

=IF(A6="",0,(LEN(A6)-LEN(SUBSTITUTE(A6,",","")))+1)

You can then just SUM column C to find out how many jobs in total.

The formula is looking at the total length of the characters in the cell and then working out the length without the commas; one less the other plus 1 tells you how many entries (plus 1 because last entry doesn't have a comma after it).

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40509503
Not quite as simple, and certainly not as easily explained to other users, you can use the following Array Formula in C3 with no need for other entries in column C:

=SUM(IF(A6:A82="",0,(LEN(A6:A82)-LEN(SUBSTITUTE(A6:A82,",","")))+1))

Confirm entry with Shift + Ctrl + Enter and curly brackets  {  } will appear enclosing the formula.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40510922
Probably simpler to use Z in the substitute function, like Rory did, then don't need the +1 at the end.
0
 

Author Closing Comment

by:WalterAPO
ID: 40517057
Thank you your solution worked great.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question