跳到主要內容

如何計算多個工作表中的特定值？

假設我有多個工作表，其中包含以下數據，現在，我想從這些工作表中獲取特定值“ Excel”的出現次數。 如何在多個工作表中計算特定值？

跨多張紙的文檔計數 1 跨多張紙的文檔計數 2 跨多張紙的文檔計數 3 2 跨多張紙的文檔計數 4

用公式計算多個工作表中的特定值

使用Kutools for Excel在多個工作表中計算特定值

  用公式計算多個工作表中的特定值

在Excel中，有一個公式供您計算多個工作表中的某些值。 請執行以下操作：

1。 在單列中列出所有包含要計數的數據的工作表名稱，如以下屏幕截圖所示：

跨多張紙的文檔計數 5

2。 在空白單元格中，請輸入以下公式： =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))，然後按 Enter 鍵，您將在這些工作表中獲得值“ 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, Sheet10 和 Sheet15 是您要計算的工作表， D2 是您基於的條件），然後按 Enter 獲得結果的關鍵。 看截圖：

跨多張紙的文檔計數 7

使用Kutools for Excel在多個工作表中計算特定值

如果你有 Excel的Kutools，其 導航 窗格中，您可以快速列出併計算多個工作表中的特定值。

Excel的Kutools : 帶有300多個便捷的Excel加載項，可以在30天內免費試用. 
前往下載
30天免費試用購買
貝寶/ MyCommerce

安裝後 Excel的Kutools，租約請執行以下操作：

1。 點擊 庫工具 > 導航，請參見屏幕截圖：

跨多張紙的文檔計數 9

跨多張紙的文檔計數 10

2。 在 導航 窗格，請執行以下操作：

（1.）點擊 查找和替換 按鈕以展開 查找和替換 窗格

（2.）將特定值鍵入 查找內容 文本框;

（3.）從 惠廷 下拉，在這種情況下，我會選擇 選定的工作表;

（4.）然後從中選擇要計算特定值的紙張 工作簿 列錶框;

（5.）檢查 匹配整個單元格 如果要計算單元格匹配的精確度；

（6.）然後單擊 找到所有 按鈕以列出多個工作表中的所有特定值，並且單元格的數量顯示在窗格的底部。

立即下載和免費試用Excel的Kutools！

演示：使用Kutools for Excel在多個工作表中計算特定值

Excel的Kutools：具有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選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (15)
No ratings yet. Be the first to rate!
Fisher
Fisher
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?
Reply
0
0
Leek
Leek
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?
Reply
0
0
skyyang
skyyang     Leek
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!
Reply
Report
0
0
Francisco
Francisco
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
Reply
0
0
Fabian
Fabian
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?
Reply
0
0
Bhupendra
Bhupendra
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.
Reply
0
0
emran
emran
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.
Reply
0
0
Mike
Mike
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")
Reply
0
0
Hari
Hari     Mike
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
Reply
0
0
David
David
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?
Reply
0
0
foxx
foxx
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.
Reply
0
0
matt
matt     foxx
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
Reply
0
0
juntjoo
juntjoo
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)
Reply
0
0
matt
matt     juntjoo
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
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations