WalterAPO
asked on
Excel 2010 Countif problem
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
ZZZ-Job.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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
Walter
Your existing formula can still work - Just do a Text to Columns - to have the Z Jobs in individual cells - Delimiter being Comma
If you want a simple formula you need a simple data layout.
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(S UBSTITUTE( 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
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(S
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
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(A 6:A82)-LEN (SUBSTITUT E(A6:A82," ,","")))+1 ))
Confirm entry with Shift + Ctrl + Enter and curly brackets { } will appear enclosing the formula.
Thanks
Rob H
=SUM(IF(A6:A82="",0,(LEN(A
Confirm entry with Shift + Ctrl + Enter and curly brackets { } will appear enclosing the formula.
Thanks
Rob H
Probably simpler to use Z in the substitute function, like Rory did, then don't need the +1 at the end.
ASKER
Thank you your solution worked great.