2

I have a workbook with multiple sheets. One is a MASTER sheet with all of the information with various columns of various pieces of info. There are other sheets that are counting various cells throughout the MASTER sheet, and I am already using COUNTIFS to accomplish this, but what I would like to do is create queries that will create sums based off the color of the text from one column, given that they meet the requirement of having certain info in a different column.

For example:

This is a list of various personnel. Each person belongs to a different section. They also complete different courses of training at different times (represented by BLACK font), while some are pending certain courses of training (RED), and some are currently in training (BLUE)

What I would like to do is on the tracking sheet, have a 3 cells track each color in a given column, based on the section they are in.

While I am familiar with COUNTIFS, and I can also set a VB module to create a function to count the cells on the same sheet, I just can't seem to make it work across different sheets.

Filburt
  • 17,626
  • 12
  • 64
  • 115
righteous_fire
  • 21
  • 2
  • 1
    [For Reading Reference](http://stackoverflow.com/questions/15887257/how-to-count-up-text-of-a-different-font-colour-in-excel) – Siddharth Rout Dec 12 '13 at 00:47

2 Answers2

2

With two sheets the same (for the example) except Sheet1 without the block showing the count:

SO20530468 example

where the formula in F2 shown is:

=COUNTIF(B:B,D2)+COUNTIF(Sheet1!B:B,D2)

Courtesy Siddharth Rout.

Note you would need to save this as .xlsm to preserve the defined name.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Sorry. While reading over my initial question, it didn't look clear enough. I need to only count the number of cells in a given column containing certain color text. All the counted cells would be in the MASTER sheet and the results would be generated in 1 of another 3 sheets depending on which SECTION they belonged to. [Here's a link](https://skydrive.live.com/redir?resid=649671D301A82824!176&authkey=!AHJB6rwfTOPvtBc&ithint=file%2c.xlsm) – righteous_fire Dec 12 '13 at 14:45
0

The nice method used by pnuts, Siddarth, Siddarth, relies on two resources:

  1. A method for detecting the text color, =GET.CELL(...).

  2. A method for referencing the appropriate cells, OFFSET(INDIRECT("RC",FALSE),0,-1).

Regarding #2, there is another option:

OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)

PS: It gives exactly the same result, and I personally find this use of INDIRECT easier to understand (the other use comes from the legacy XL4, and it is not documented in current versions). I even use similar formulas, sometimes combined with ADDRESS, directly in worksheet cells.

PS2: This interesting link suggests appending +NOW()*0 to the =GET.CELL(...) formula to ensure automatic recalculation. I found in Excel 2007 it is not needed, for any of the two options for #2 (did I miss something?). It shows also other nice tricks for referring to ranges.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185