跳到主要內容

如何在Excel中基於帶有條件的過濾器對單元格進行計數/求和?

實際上,在Excel中,我們可以使用COUNTA和SUM函數在正常數據范圍內對單元格進行快速計數和求和,但是這些函數在過濾情況下將無法正常工作。 要基於過濾器或具有條件的過濾器對單元格進行計數或求和,本文可能會對您有所幫助。

基於公式的過濾器對單元格進行計數/求和

使用Kutools for Excel基於過濾器計數/求和

使用公式,根據具有特定條件的過濾器對單元格進行計數/求和


基於公式的過濾器對單元格進行計數/求和

以下公式可以幫助您快速,輕鬆地對過濾後的單元格值進行計數或求和,請這樣做:

要從過濾的數據中計算單元格,請應用以下公式: =小計(3,C6:C19) (C6:C19 是要從中進行過濾的數據范圍),然後按 Enter 鍵。 看截圖:

基於過濾器1的文檔數

要基於過濾後的數據對單元格值求和,請應用以下公式: =小計(9,C6:C19) (C6:C19 是要求和的要過濾的數據范圍),然後按 Enter 鍵。 看截圖:

基於過濾器2的文檔數


使用Kutools for Excel基於過濾器計數/求和

如果你有 Excel的Kutools是, 可數可見的 函數還可以幫助您同時計算和匯總過濾後的單元格。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用. 

安裝後 Excel的Kutools,請輸入以下公式以對已過濾的單元格進行計數或求和:

計數過濾的單元格:= COUNTVISIBLE(C6:C19)

對過濾後的單元格求和:= SUMVISIBLE(C6:C19)

基於過濾器3的文檔數

保養竅門:您還可以通過點擊以下按鈕來應用這些功能 庫工具 > Kutools函數 > 統計與數學 > 平均/可觀察/可觀察 根據需要。 看截圖:

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


使用公式,根據具有特定條件的過濾器對單元格進行計數/求和

有時,您希望在過濾的數據中根據條件進行計數或求和。 例如,我有以下過濾數據,現在,我需要計算和加總名為“ Nelly”的訂單。 在這裡,我將介紹一些公式來解決它。

基於過濾器5的文檔數

根據具有某些條件的過濾器數據對單元進行計數:

請輸入以下公式: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), --( B6:B19="Nelly"))B6:B19 是您要使用的過濾數據,以及文本 Nelly 是您要依據的條件),然後按 Enter 獲得結果的關鍵:

基於過濾器6的文檔數

根據具有某些條件的過濾器數據求和單元格:

要根據條件對C列中的過濾值求和,請輸入以下公式: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),( B6:B19="Nelly")*(C6:C19)) (B6:B19 包含您要使用的條件,文本 Nelly 是標準,並且 C6:C19 是您要求和的單元格值),然後按 Enter 鍵返回結果,如下所示:

基於過濾器7的文檔數

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
First of all, sorry for my bad English haha.

I used Skyyang's formula, but I'm still missing a small part in my formula. I entered the following formula, but it doesn’t recognize my 2nd criteria in the answer. Can you help me further? I can share the sheet if you want.

=SOMPRODUCT(((D4:D45="Auto")+(E4:E45="Zonder vervanging")*(SUBTOTAAL(3;VERSCHUIVING(E4;RIJ(E4:E45)-RIJ(E4:E45)-3;)))))
This comment was minimized by the moderator on the site
Does anybody knows how to do this but with more than one criteria? I mean, if I wanted to COUNT only the rows which qualify for two or more criterias?
Thanks a bunch!
This comment was minimized by the moderator on the site
Hello, WN,
May be the following formula can help you:
=SUMPRODUCT( ( (B2:B23="Large")+(B2:B23="Small"))*(SUBTOTAL(103,OFFSET(B2,ROW(B2:B23)-MIN(ROW(B2:B23)),0))))

If you have more criteria, just join the criteria with + character.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
If i have data in sheet 1 in trying to pull into sheet 2 that comes from a range in a column i want but I'm only looking for data that had a certain value of "system issue" and i want the second sheet to be able to see how many in that column had system issue but *** up the paid amounts in a separate column of sheet one that filter to the "system issue" so we can see how much has been paid out for system issues, thoughts on the formula? The one you keep sharing is only for 1 sheet and you keep using a number after the first parenthesis that i do not know how you came up with it.
This comment was minimized by the moderator on the site
Can this be done with more than one criteria? I mean, I know it can be as per the below answered questions. I have to have sum based on two criteria's One criteria in Row B as "RN" and another one in row DX as "D" and the sum is going to be in row EA. Any help would be great!
This comment was minimized by the moderator on the site
Does anybody knows how to do this but with more than one criteria? I mean, if I wanted to SUM only positive values?
This comment was minimized by the moderator on the site
Hi, Bernardo,
To solve your problem, you should apply below formula:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B14)-ROW(B2),0)),--(A2:A14="Lucy"),--(B2:B14>0))

Please try, hope it can help you!
This comment was minimized by the moderator on the site
It's absolutely ridiculous that EXCEL requires the formula to be so complicated! All that should be needed is a SUBTOTAL(9,Range) WHERE/HAVING criteria (X,Y,Z).
This comment was minimized by the moderator on the site
Yes I agree, I am looking for the Excel formula so that the sum of the range (with criteria) is NOT affected by the Filtered columns.
This comment was minimized by the moderator on the site
I agree. It is ridiculous
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations