Count Specific word in Specific range

  • Thread starter Thread starter sizz1
  • Start date Start date
S

sizz1

Hi i need a little help with some excel formulas

1:
I need a formula which will calculate a specific word in excel
(specific word=Industrial)
I have already created a formula
=COUNTIF(H10:E12,"*industrial*")
It works fine in one specific range (E.g H10:E12). I need to add
another range in that formula. Everytime i try, i get an error message.
E.G H10: E12 AND F14:G20

2:
In the same spreadsheet i have filled in some cells with different
colours, using the fill tool
Red
Blue
Green

I need a forumla which will calculate each Red cell in a given range?
I need a forumla which will calculate each Blue cell in a given range?
I need a forumla which will calculate each Green cell in a given range?

Again the range i need will be two different ranges. E.g A1:B5 and
D1:D5

I look forward to your response
 
Hi

Use

=COUNTIF(A1:D6,"*industrial*")+COUNTIF(A11:E17,"*industrial*")

to count the colored cells here is one example

Dim c As Range
numred = 0
numblue = 0
numgreen = 0
For Each c In Range("a1:b5", "d1:d5")
Select Case c.Interior.ColorIndex
Case 3
numred = numred + 1
Range("e1") = numred
Case 5
numblue = numblue + 1
Range("e2") = numblue
Case 10
numgreen = numgreen + 1
Range("e3") = numgreen
End Select
Next c

you might need to change the colorindex to the shade of the color you
use

Cheers Christian
 
Back
Top