Count colours in several set of rows

[John_Davies]

Hi good people of Excel land. I'm new here and hope you can help. I've used Excel in a basic form for a while, but am getting to use more and more in depth functions.

I have 48 rows of data, each row has 3 cells. Each row details information about sensors. I want to count the sensors into three groups: Good, indeterminate and bad.

I have two reference cells above each column, an upper limit and a lower limit:

590 170 20
570 165 18

xxx yyy zzz
xxx yyy zzz
xxx yyy zzz
xxx yyy zzz
. . .
. . .
. . .

I use conditional formatting to colour the cells thus:
cell style GOOD for any cells >= the upper limit.
cell style NEUTRAL for any cells between the limits.
cell style BAD for any cells < the lower limit.

So far so good.

Now I want three cells set up to count the rows with the following criteria (each row is one sensor):

Cell #1: ALL cells in a row are GOOD (This sensor is a pass).
Cell #2: One or more cells in a row are NEUTRAL (Sensor is indeterminate).
Cell #3: One or more cells in a row are BAD. (No prizes for guessing this one).

I generate data that has every combination of these. I don't want the same row to be deemed as NEUTRAL and BAD which and give incorrect counts.

if this needs macros then no problem.

Any help appreciated.

Thanks,

John.

[AliGW]

Welcome to the forum!

You can't count colours with a formula. You will need to count something else.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

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 results are 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.

[bebo021999]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Pete_UK]

As you are using conditional formatting to generate the colours, you can use the same conditions within a COUNTIF function to count how many occur.

With a sample Excel workbook, it should be easier to set this up for you.

Pete

[John_Davies]

Hi,

Please find attached an example of the data and colouring.

I'm aware that counting based on colours is not the way to go and I have tried COUNTIFS to achieve the goal, but it didnta take into account the adjacent call data and either didn't count at all, or couted incorrectly.

The actual counts in the example are manual.

John.

[AliGW]

Cell #2: One or more cells in a row are NEUTRAL (Sensor is indeterminate).
Cell #3: One or more cells in a row are BAD. (No prizes for guessing this one).

How will you break a tie? What if there is one of each in the row, along with a green - what should the outcome be?

[Pete_UK]

It might be easier to use column F as a helper to check each sensor - put this formula in F7:

=IF(AND(C7>=C$2,D7>=D$2,E7>=E$2),"Good",IF(OR(C7<=C$4,D7<=D$4,E7<=E$4),"Fail","?"))

then copy down. Then it would be quite easy to generate your summary table using COUNTIF.

Hope this helps.

Pete

[Pete_UK]

@Ali,

As I understand it, only if ALL 3 are green does it pass, and if any single one is red then it fails.

Hope this helps.

Pete

[John_Davies]

Hi,

If there is at least one NETURAL in a row (the rest GOOD), then the sensor is indeterminate, so the indeterminate count increments by 1.
If there is at least one BAD (The others can be GOOD or NEUTRAL) the sensor is bad, so the bad count increments by 1.

John.

[John_Davies]

....and yes, if all cells in a row are GOOD, the good count increments by 1.

John

[John_Davies]

Hi Pete,

Extreme thanks for coming up with this! I'll hide the column and as you said, do a count from this.

Perfect.

John.

[AliGW]

I don't think you need a helper column - I have almost worked out a solution. Please hold fire!

[John_Davies]

I is holdin'.....

The helper column works but I'm open to all help!

john.

[AliGW]

I've hit a roadblock. This works for good:

=COUNTIFS($C$7:$C$54,">="&C2,$D$7:$D$54,">="&D2,$E$7:$E$54,">="&E2)

I am just working out the other two.

[John_Davies]

Many thanks for all help so far!

I need to account for blank cells and not include them in the counts.

The results are copy & pasted from the tester software, so all three cells are populated one row at a time.

Just to make your job more interesting.

John.

[AliGW]

... and on that bombshell ...

The garden is calling me. Feel free to mark this as solved!

[AliGW]

Just out of curiosity, you said this:

I need to account for blank cells and not include them in the counts.

If one or more of the three in a row are blank, what should happen?

1. The row is entirely disregarded?

2. 2 green and a blank returns the same as 3 green?

3. Something else?

[AliGW]

Anyway, it can be done without a helper column as it stands.

In H2: =COUNTIFS($C$7:$C$54,">="&C2,$D$7:$D54,">="&D2,$E$7:$E$54,">="&E2)

In H3: =ROWS($C$7:$E$54)-H2-H4

In H4: =SUM(--(MMULT(--($C$7:$E$54<$C$4:$E$4),TRANSPOSE(COLUMN($C$7:$E$54)))>0))

H4 needs to be confiurmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Excel 2016 (Windows) 32 bit

	G	H
2	Good:	13
3	?:	3
4	Bad:	32

Sheet: Sheet1

[John_Davies]

Hi and thanks for the help. It will be Tuesday before I can try it do will be back to let you know how it went.

John.

[John_Davies]

Hi AliGW,

This works a treat so will implement it forth, fifth & sixth with.

John.

[AliGW]

Glad to have helped and thanks for letting me know.