Counting problems: count rang values while condition is true

Dear Members of the excel Forum,

I'm working on my school project in excel and I have some issues which I can't resolve. I want to count some cells in my excel sheet but with some conditions. I have to count only valid values. The values in columns cells are contains some strings which are valid or not (etc. ss, w, cb, f, .. are all valid values, but B and Z are not).

Actions: While the rang cells are under the counting, the counting result must meet some condition (etc. result of valid scan need to be equal 50). The key point is If the condition is true, than the counting need to be break and function result must be returned.

I have a several columns (with the values mentioned above) which doesn't have a same rang.

I tyed to use some conbination of the function "=COUNTIF(A2:A134,IF((COUNTA(A2:A134)-COUNTIF(A2:A134,"Z")-COUNTIF(A2:A134,"B")),"=50", and than to paste this formula for another column, but in vain.

Link to excelSheet:
https://drive.google.com/file/d/0B_M...RqbjFzY2M/view


Thanks allot!

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

This is a fairly complex problem. Is this a University class? First issue is that COUNTIF(S) are not case sensitive so if you need to distinguish between "b" and "B", they won't do it.
Are you allowed to use helper columns? In your worksheet, you are only looking for "Z" and "b" correct? And you want to stop counting if the total hits 50? Do you need to know what row it hit's 50 at? or is it simply that the maximum value can be 50?

Dear ChemistB,

First of all, I would like to thank you for your interesting in my presented issues. The value of "Z" and "B" are not correct or valid in the columns. In my excel sheet I only looking for a valid values, and not for all the values in column COUNT1. In the attached table, valid values are "ss, w, cb, f", and so on, and not valid values are only "B" and "Z". In my represented function, values "B" and "Z" are recognize in the functions as a not valid values and then all of it are subtracted from the rest of the counted total scan. The counting must be stopped when condition is true (when a valid scans equal 50). I hope I explained well now.

Does this work for you? I used conditional formatting to color cells green as you go down the column. As soon as the cell stops turning green, you've reached your 50 stop point.

I used this formula in the conditional formatting
=COUNTA($A$2:$A2)-COUNTIF($A$2:$A2, "Z")-COUNTIF($A$2:$A2, "b")<=50