在同一工作簿中的不同工作表中的各列中计算特定颜色文本的单元格 [英] Counting cells of particular color text in various columns in different sheets in the same workbook

问题描述

我有一个包含多个工作表的工作簿。一个是MASTER表，所有信息和各种信息的各个列。还有其他工作表计算MASTER表中的各种单元格，我已经使用 COUNTIFS 来完成这一操作，但我想做的是创建查询基于来自一列的文本的颜色，基于它们满足在不同列中具有某些信息的要求来求和。

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.

例如：

这是各种人员的列表。每个人属于不同的部分。他们还在不同时间完成不同的培训课程（以黑色字体表示），有些正在等待某些培训课程（RED），有些正在培训（BLUE）。

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)

我想要做的是在跟踪工作表上，根据它们所在的部分，在给定列中有3个单元格跟踪每种颜色。

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.

虽然我熟悉 COUNTIFS ，我也可以设置一个VB模块来创建一个函数来计数在同一工作表上的单元格，我只是似乎无法做到工作在不同的表。

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.

推荐答案

除了Sheet1外，两张图片相同（示例），但不显示计数：

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

其中F2中的公式为：

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

感谢Siddharth Rout。

Courtesy Siddharth Rout.

注意，您需要将其保存为.xlsm以保留定义的名称。

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

这篇关于在同一工作簿中的不同工作表中的各列中计算特定颜色文本的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！