CODEWITHSUNDEEP
excelvbaexcel-formula
Renin Mathew
Renin Mathew

Reputation: 47

Count number of cells with specifc text and specific colour in excel range where the text is derived from another column

Perhaps easier looking at the data,

ExcelTable

I basically need to know how many cells in red are under each country using a formula or VBA. I was able to get a function off the internet which counts the total number of red background cells but this does not do the job. I need the number of red cells by country. My output should look like this,

enter image description here

Any help would be highly appreciated!

Upvotes: 0

Views: 527

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

Here is a formula solution without helper column, please see

1] Assume "Source table" put in A1:B26 include "Status" column filled with red and green background color

2] "Output table" put in A29:B34 include criteria country put in range A30:A34 (Argentina, China, Australia....)

3] In "Output" header criteria B29, of wording "RED" must filled with red background color

4] "Output" select B30 >> Define Name >>

'>> Name : CountColor

'>> Refer to :

=SUMPRODUCT(($A$2:$A$26=$A30)*(GET.CELL(63,IF(1,+OFFSET($B$2:$B$26,ROW($B$2:$B$26)-ROW($B$2),)))=GET.CELL(63,$B$29)))

Then,

5] In "Output" B30, enter formula and copied down :

=CountColor

6] Finally save file in xlsm type

Edit : Get.Cell is a Excel 4 Macro function, you need to save the file as Macro-Enable Workbook xlsm type.

Upvotes: 1

Related Questions