Counting the number of Cells with a Particular Background Colour?

H

Hamish

Hi

I have an Excell sheet with about 200 lines. My results are calssified in
Columns in 5 Different colours. I would like to automatically count the
number of cells with a particular background colour in each coloumn.

For example:

Look at one whole column and count how many cells have a green background.

is this possible?
 
H

Hamish

I would like the extend this now. The above link works fine, thankyou.

How would I change this to be conditional. For example

I have 2 Coloumns, The first coloumn contains a list of numbers

1.1 (req)
1.2 (adv)

I would like to count the number of Cells that are a particular colour in
Coloumn two and are of type (req) in the same row of colomn 1 and then the
same for adv in colomn 1. I have some ideas but I am only Familiar with C.

I would be greatful for some help. Thanks
 
B

Bob Phillips

Something like

=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3),--(A1:a100="adv"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
H

Hamish

Thanks for the idea, it does not work, when you select the range of the
--(A1:a100="adv") part the range is not in blue.

will the "adv" mean that the cell contains that exactly or it just has to be
part of the cell contents?

Maybe I will just have to count manually, /cry



Bob Phillips said:
Something like

=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3),--(A1:a100="adv"))
[/QUOTE]
 
B

Bob Phillips

No never count manually.

That formula is counting cells that are red, and equal 'adv', not
containing, but equal.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hamish said:
Thanks for the idea, it does not work, when you select the range of the
--(A1:a100="adv") part the range is not in blue.

will the "adv" mean that the cell contains that exactly or it just has to be
part of the cell contents?

Maybe I will just have to count manually, /cry
[/QUOTE]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top