Count of Cell Highlighting over Multiple Columns per Name

[MacClaus]

Dear all

Thank you so much for this forum.

I have a list of about 1000 rows which contains Names (the name of the person entering information) and the information entered per row, over 75 columns.
Based on the data entered, some cells are highlighted red (this is the only type of highlighting), but the data and condiitons for the highlighting may vary (all the same in the example).
I would like to know how many cells per Name are highlighted, counting/summing all of the highlights of a Name's row(s).
The result is to be provided on another tab - see example. Note I have not filled the result, hope this is self-explanatary.

Can this be done with Excel FUNCTIONS only? I do not want to use VBA.

Many thanks.

Kind regards,
Claus.

[Richard Buttrey]

If all the names on the Result sheet are in the same order as the Source Sheet then

B2

=COUNTIF(Source!B2:BX2,">"&96)

However since they're not

=COUNTIFS(OFFSET(Source!$A$1:$A$1001,MATCH(A2,Source!A:A,FALSE)-1,0,1,75),">"&96)

[MacClaus]

Hi Richard

Thanks for your reply, but I can not rely on the data - only the highlighting - as mentioned:

Based on the data entered, some cells are highlighted red (this is the only type of highlighting), but the data and condiitons for the highlighting may vary (all the same in the example).

Sorry, but this solution will not work.

Kind regards,
Claus.

[Richard Buttrey]

Just realised the order is not the same. See my addendum in #2

[MacClaus]

Hi Richard

Thank you, but again, I can NOT rely on the data in the cells, ONLY the highlighting.
In that case I believe your solution will not work.

Kind regards,
Claus.

[AliGW]

If the cells are highlighted, then the cell value is over 96, so for Emma:

=SUMPRODUCT((Source!$B$2:$BX$1001>96)*(Source!$A$2:$A$1001=A2))

Copy down.

If this isn't it, you are going to need to explain in more detail.

In future, please provide sample datasets of no more than 20 rows of data.

[MacClaus]

Dear all

Please, you can not rely on the DATA - as the title says, COUNT OF CELL HIGHLIGHTING.
I want to count the cells that are highlighted, not the cells that contain a cetain value (i.e. greater than 96).

Hope that is clear.

Many thanks.

Kind regards,
Claus.

[AliGW]

Just seen this:

condiitons for the highlighting may vary (all the same in the example).

This makes me fume - sorry. PLEASE provide REALISTIC sample data!!!

[AliGW]

I want to count the cells that are highlighted,

You can't with a formula.

[MacClaus]

Hi AliGW!

Yesterday you asked me to make sure that the data set (sample file) is represnettaive of the live data set - I had not informed that the matrix can be huge and we hit the 37'400 character limit.
Today I create a more representative set, now you tell me to not make it so big.
How can I win?

Kind regards,
Claus Steffen.

[MacClaus]

Hi AliGW

I appreciate your support, but please, this reply is unnecessary. I EXPLAINED IT MANY TIMES, and I USED the CORRECT TITLE.
Please just read the request.

Kind regards,
Claus.

[AliGW]

Yesterday you asked me to make sure that the data set (sample file) is represnettaive of the live data set

However, you have NOT made it representative.

I USED the CORRECT TITLE.

I have said nothing about your title!

How can I win?

By giving us data that we can actually work with. If the REAL data has differing values assigned in the conditional formatting, then the SAMPLE data needs to reflect that. We cannot provide working solutions with half-baked data!

this reply is unnecessary.

Which reply? The one where I expresed my frustration or the one where I told you it can't be done with a formula?

Please just read the request.

I did read the request.

[Richard Buttrey]

Hi Richard

Thank you, but again, I can NOT rely on the data in the cells, ONLY the highlighting.
In that case I believe your solution will not work.

Kind regards,
Claus.

The highlighting is BASED on the data in the cells. If you can't rely on the data then you can't rely on the highlighting.

Please clarify what you are saying. Are you suggesting that the highlighting of values over 96 is not consistent and some cells that are not >96 are nevertheless highlighted?

Or are you saying that with other worksheets the value being tested for highlighting is something other than 96. If so just build in a cell that contains the mumber and use that cell in the formulae given.

[AliGW]

Richard - the OP said this in the opening post (which I did not fully appreciate on first reading):

condiitons for the highlighting may vary (all the same in the example).

I understand from this that the real data contains multiple CF rules, but the sample data contains only one.

[MacClaus]

Dear all

Thanks, but let's get back to reading the request.
The reqeust was to COUNT the DATA HIGHLIGHTING and I stated that the data is NOT representative.

Kind regatds,
Claus.

[AliGW]

As I have already told you, you cannot count highlighting with a formula. This is why your helpers are trying to find something else to use, as you have said you do not wish to use VBA.

The sample data SHOULD be representative - there is no point whatsoever in suppling sample data that isn't.

EDIT: you have marked the thread as solved. Do you not require further help with this?

[MacClaus]

Hi AliGW

I have marked the thread as SOLVED.

Kind regards,
Claus.

[AliGW]

Why? Do you not want any further help with this? We are trying very hard to help you.

[MacClaus]

Hi AliGW

I really appreciate the help, but you said it's not possible with FUNCTIONS - hence I closed it.

Kind regards,
Claus.

[AliGW]

I said that using the highlighting in a formula won't work, but if you were to share properly representative data, we might be able to find another way.

Thanks for letting us know.