Counting conditionally formatted cells

I have a main spread sheet which has cells that change background colour via condition formatting and I would like to count the different colour cells in a column. The main spreadsheet is linked to other worksheets and its the data on them which triggers the conditional formatting. I have researched the forums etc on how to count conditionally formatted background colours in a column and none of their solutions seem to work. In an attempt to find out what is wrong I have run a program which shows the numeric value of the cell according to its background colour on the main sheet, and the value doesnt change when the conditional format changes the cell colour. The only thing I can think of is that because the data is pulled from another worksheet somehow this is messing up the "normal" conditional formatting count formulas I have found. I have read that you can use the formula which triggers the conditional format with countif but being very inexperienced i have not been able to figure this out. This is the conditional formatting formula =if (and(I11="m"),today()-O$7>N11) Sorry its long winded but can anybody help me with this please?

Originally Posted by Nigel

I have a main spread sheet which has cells that change background colour via condition formatting and I would like to count the different colour cells in a column. The main spreadsheet is linked to other worksheets and its the data on them which triggers the conditional formatting. I have researched the forums etc on how to count conditionally formatted background colours in a column and none of their solutions seem to work. In an attempt to find out what is wrong I have run a program which shows the numeric value of the cell according to its background colour on the main sheet, and the value doesnt change when the conditional format changes the cell colour. The only thing I can think of is that because the data is pulled from another worksheet somehow this is messing up the "normal" conditional formatting count formulas I have found. I have read that you can use the formula which triggers the conditional format with countif but being very inexperienced i have not been able to figure this out. This is the conditional formatting formula =if (and(I11="m"),today()-O$7>N11) Sorry its long winded but can anybody help me with this please?

Untested but how about something like:

Please Login or Register  to view this content.

HTH

Many many thanks for your help....Its late here now so I will try your suggestions tomorrow.

Kind regards

I tried =Sumproduct(--(I11:I100="m"),--(today()-O$7>N11:N100)) and it works perfectly, however the formula I gave as an example was just a section of the actual conditional format I am using. Sorry I thought it would be enough to give you an idea of what I am trying to do, Im now having trouble adapting your solution to the full formula which is

=IF(AND(I11="M"),TODAY()-O$7>=N11,IF(AND(I11="C"),TODAY()-O$6>=N11,IF(AND(I11="B"),TODAY()-O$5>=N11,IF(AND(I11="A"),TODAY()-O$4>=N11))))

If you could show me how to use "sumproduct" on that lot I would be very grateful

Many thanks and regards

Originally Posted by Nigel

I tried =Sumproduct(--(I11:I100="m"),--(today()-O$7>N11:N100)) and it works perfectly, however the formula I gave as an example was just a section of the actual conditional format I am using. Sorry I thought it would be enough to give you an idea of what I am trying to do, Im now having trouble adapting your solution to the full formula which is

Please Login or Register  to view this content.

If you could show me how to use "sumproduct" on that lot I would be very grateful

Many thanks and regards

Hi, this is untested but you simply need to replace the IFs by placing the subsequent test in another set of brackets prefaced with the -- double unary operator. e.g.

Please Login or Register  to view this content.

You'd need to tweak Richard's suggestion a little to get the correct count, I think. I.e.

=SUMPRODUCT((I11:I100="M")*(TODAY()-O$7>=N11:N100)+(I11:I100="C")*(TODAY()-O$6>=N11:N100)+(I11:I100="B")*(TODAY()-O$5>=N11:N100)+(I11:I100="A")*(TODAY()-O$4>=N11:N100))

But you could use a slightly different approach

for your conditional formatting formula you could use

=TODAY()-INDEX(O$4:O$7,MATCH(I11,{"A","B","C","M"},0))>=N11

and, if you list the alphabetic values in N4:N7, i.e. N4="A", N5="B", N6="C" and N7="M" then you could use this formula for a count

=SUMPRODUCT((I11:I100<>"")*(TODAY()-SUMIF(N4:N7,I11:I100,O4:O7)>=N11:N100))

Hi... many thanks for the replies. I copied your solution "Daddylonglegs" and it has solved my problem so thanks for that. Just as a matter of interest I did like the tidy formula you suggested for my conditional formatting and gave it a go but got the error message "You may not use unions, intersections or array constants for conditional formatting criteria". Despite my best efforts your formula is way above my basic level so I left well alone, but thought you might be interested in the feedback.

Thanks again for your help

Regards