跳到主要内容

如何计算多个工作表中的特定值?

假设我有多个工作表,其中包含以下数据,现在,我想从这些工作表中获取特定值“ Excel”的出现次数。 如何在多个工作表中计算特定值?

跨多张纸的文档计数 1 跨多张纸的文档计数 2 跨多张纸的文档计数 3 2 跨多张纸的文档计数 4

用公式计算多个工作表中的特定值

使用 Kutools for Excel 计算多个工作表中的特定值


  用公式计算多个工作表中的特定值

在Excel中,有一个公式供您计算多个工作表中的某些值。 请执行以下操作:

1。 在单列中列出所有包含要计数的数据的工作表名称,如以下屏幕截图所示:

跨多张纸的文档计数 5

2。 在空白单元格中,请输入以下公式: =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2)),然后按 输入 键,您将在这些工作表中获得值“ Excel”的数字,请参见屏幕截图:

跨多张纸的文档计数 6

笔记:

1.在上式中:

  • A2:A6 是要在工作表中计算指定值的数据范围;
  • C2:C4 是工作表名称列表,其中包含要使用的数据;
  • E2 是您要基于的条件。

2.如果需要列出多个工作表,则可以阅读本文 如何在Excel中列出工作表名称? 处理这个任务。

3.在Excel中,您还可以使用COUNTIF函数一个接一个地添加工作表,请使用以下公式: =COUNTIF(Sheet1!A2:A6,D2)+COUNTIF(Sheet10!A2:A6,D2)+COUNTIF(Sheet15!A2:A6,D2)Sheet1, Sheet10Sheet15 是您要计算的工作表, D2 是您基于的条件),然后按 输入 获得结果的关键。 看截图:

跨多张纸的文档计数 7


使用 Kutools for Excel 计算多个工作表中的特定值

如果你有 Kutools for Excel,其 旅游导航 窗格中,您可以快速列出并计算多个工作表中的特定值。

Kutools for Excel : 带有300多个便捷的Excel加载项,可以在30天内免费试用. 

安装后 Kutools for Excel,租约请执行以下操作:

1。 点击 库工具 > 旅游导航 ,请参见屏幕截图:

跨多张纸的文档计数 9

跨多张纸的文档计数 10

2。 在 旅游导航 窗格,请执行以下操作:

(1.)点击 查找和替换 按钮以展开 查找和替换 窗格

(2.)将特定值键入 查找内容 文本框;

(3.)从 惠廷 下拉,在这种情况下,我会选择 所选工作表;

(4.)然后从中选择要计算特定值的纸张 工作簿 列表框;

(5.)检查 匹配整个单元格 如果要计算单元格匹配的精确度;

(6.)然后单击 找到所有 按钮以列出多个工作表中的所有特定值,并且单元格的数量显示在窗格的底部。

立即下载和免费试用Excel的Kutools!


演示:使用 Kutools for Excel 计算多个工作表中的特定值

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!


相关文章:

如何在Excel中使用countif来计算百分比?

如何在Excel中对多个条件进行计数?

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, I've used the =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2)) successfully to total criteria for sheetlists by month for 2023. I want to continue on the same workbook for 2024 and make new sheetlists by month. When i try the above formula with 2024 sheetlists, I get an REF error that i cant figure out how to resolve. Is there any way to resolve the error or do i have to start a new workbook?
This comment was minimized by the moderator on the site
Ik krijg alleen maar foutmeldingen na het kopieren-plakken van het voorbeeld en lezen van de tutorial. Ook na cellen veranderen en dergelijke.

Hoe kan ik de getallen in cellen over meerdere sheets/tabbladen bij elkaar optellen?
This comment was minimized by the moderator on the site
Hi, Leek,
to sum values from multiple worksheets, please apply htis formula: =SUM(Sheet1!B5,Sheet2!B5,Sheet3!B5…)

Notes:
1. In this formula, Sheet1!B5, Sheet2!B5, Sheet3!B5 are the sheet name and cell value that you wan tto sum, if there are more sheets, you just add them into the formula as you need.
2. If the source worksheet name contains a space or special character, it must be wrapped in single quotes. For example: 'New York'!B5

Please try it, hope can help you!
This comment was minimized by the moderator on the site
Thank you. Even though is very long formula, the one that worked the easiest for me was COUNTIF + COUNTIF
This comment was minimized by the moderator on the site
This doesn't work when the sheet name has dashes in them. Anything I can do about it?
This comment was minimized by the moderator on the site
Need a answer very eagerly for this. I have multiple worksheets in our workbook, and I want to count total number for values in a column C for every sheet and that too every sheet wise not a total of every C column of all sheets.
This comment was minimized by the moderator on the site
1*1=1 but from 2 it should be 3 double. how i set up in excel? please give me a solution.
This comment was minimized by the moderator on the site
This was extremely helpful. the below formula is what i use to track all my employees Vacation,Sick,Personal,LOA days. I have tabs for every month of the year and use this to track how many days they have used. All i do is change the "V" to a "S" or "P". Formula works perfect! thanks for the info....

=COUNTIF(January!B16:AF16,"V")+COUNTIF(February!B16:AF16,"V")+COUNTIF(March!B16:AF16,"V")+COUNTIF(April!B16:AF16,"V")+COUNTIF(May!B16:AF16,"V")+COUNTIF(June!B16:AF16,"V")+COUNTIF(July!B16:AF16,"V")+COUNTIF(August!B16:AF16,"V")+COUNTIF(September!B16:AF16,"V")+COUNTIF(October!B16:AF16,"V")+COUNTIF(November!B16:AF16,"V")+COUNTIF(December!B16:AF16,"V")
This comment was minimized by the moderator on the site
I want to count specific word in Coloum accross the Jan to Dec, Please advice and sum it up
This comment was minimized by the moderator on the site
Hi. Using this formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))

I get a Ref error if Column C has more than 9 rows. In other words, INDIRECT("'"&C2:C9&"'!A2:A6"),E2)), is OK but INDIRECT("'"&C2:C10&"'!A2:A6"),E2)) is not.
How do I avoid this error?
This comment was minimized by the moderator on the site
How would I write the formula above (=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))) if I had to use the COUNTIFS function? Right now I have to see if certain cells have met 2 to 3 criteria (across multiple worksheets). cheers for you help.
This comment was minimized by the moderator on the site
try =SUMPRODUCT(COUNTIFS(INDIRECT("'"&C2:C4&"'!A2:A6"),E2),(INDIRECT("'"&C2:C4&"'!A2:A6"),X2),(INDIRECT("'"&C2:C4&"'!A2:A6"),Y2)) where E2, X2 and Y2 are your criteria and INDIRECT("'"&C2:C4&"'!A2:A6" are your criteria range, where C2:C4 are your tab names and A2:A6 is your data range let me know if it works I'm actually very curious, didn't have time to run and test it myself. I just had to use one criteria so above easier formula did work wonders for me
This comment was minimized by the moderator on the site
Can you not specify your range of cells within a RANGE of sheets instead of "sheet1+sheet2+sheet3...." and specify a range to compare to like: countif(sheet1:sheet5!b:b, a:a)
This comment was minimized by the moderator on the site
tried it, didn't work. you need your tab(sheet) names in the cells for it to work. The cells are actually making up a range themselves, hence C2:C4 in the formula
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations