一個高難度的Excel非重複值求和公式,值得你收藏

2021-02-13 Excel精英培訓

以前總會遇到多條件不重複計數的問題，即使你不會也可以在網上搜到很多種答案，但今天蘭色遇到一個看似簡單的Excel求和公式，做起來真費了不少腦筋。

如下圖所示，上表中是每個人在各個銀行開的信用卡清單，現需要統計出每個人的開卡個數和總額度之和（同一個銀行只算一次）。

開卡個數公式很簡單，用Countif統計即可

=COUNTIF(B$2:B9,A13)

總額度之和的公式很難設置，難就難在同一個銀行只能計算一次。如計算張三時，第3行的軍魂卡就不能統計在內了。

遇到這樣的問題，我們逐步設置公式：

1、根據姓名篩選銀行

=IF(B2:B9=A13,A2:A9,NA())

2、把重複銀行排除

用match函數查找位置和行數對比，重複的會返回false

=MATCH(IF(B2:B9=A13,A2:A9,NA()),IF(B2:B9=A13,A2:A9,NA()),)=ROW(1:8)

3、返回符合條件的額度

和C列相乘

=(MATCH(IF(B2:B9=A13,A2:A9,NA()),IF(B2:B9=A13,A2:A9,NA()),)=ROW(1:8))*C2:C9

4、用iferror函數錯誤值變成0

=IFERROR((MATCH(IF(B2:B9=A13,A2:A9,NA()),IF(B2:B9=A13,A2:A9,NA()),)=ROW(1:8))*C2:C9,0)

5、最後用sumprouduct函數求和


=SUMPRODUCT(IFERROR((MATCH(IF(B$2:B9=A13,A$2:A9,NA()),IF(B$2:B9=A13,A$2:A9,NA()),)=ROW($1:8))*C$2:C9,0))

蘭色說：由於時間關係公式沒有優化。蘭色覺得應該還有更簡單的公式。如果你寫出，就留言分享出來。按條件不重複值求和公式，網上很難搜到，建議大家一定要收藏起來。

如果你是新同學，長按下面二維碼 - 識別圖中二維碼 - 關注，就可以每天和蘭色一起學Excel了。

工作中最常用的Excel函數公式，全印在一張超大的滑鼠墊上(送40集配套視頻)，點我查看詳情

相關焦點

  • 集齊所有Excel求和公式,再不收藏就是你的錯了!
  • 值得收藏的6個Excel函數公式(有講解)
    收藏的Excel函數大全公式再多，幾天不用也會忘記。怎麼才能不忘？你需要了解公式的運行原理。
  • Excel多工作表不同條件篩選匯總求和公式,看過的都已果斷收藏!
    （ID：ExcelLiRui）職場辦公中什麼問題都有可能遇到，比如多表求和、篩選求和、跨表匯總.當這幾種問題同時混合在一起時，你還能順利解決嗎？今天介紹一種幾乎沒人知道的多表多條件篩選求和公式，為了大家更好理解，下面結合案例介紹。
  • 只會Sumif函數Out了,Excel常用的求和公式全在這兒!
    求和是工作中最常用的數據統計要求。今天蘭色再次進行一次大整理，把和求和有關的函數公式來一次大歸集。千萬別錯過了這次收藏的機會。
  • Excel 中最值得收藏的5個函數公式,給你整理好了!建議收藏!
    今天給大家將講5個最常用的Excel函數公式~收藏好了！上班直接套用~工作中80%都會用到的Vlookup函數！多區域求和時不必一個一個區域選擇再最後加總，用SUM函數整體框選區域即可！SUM函數將為值求和。
  • 15個Excel函數公式的套路,可直接套用,收藏備用吧
    今天跟大家分享15個Excel函數公式的套路，如果遇到類似的問題，直接套用即可，話不多說我們開始吧1. 查找重複值公式：=IF(COUNTIF(A$2:A2,A2)=1,"","重複")首選我們利用countif函數進行條件計數，然後使用if函數進行判斷當其結果等於1時代表不重複，當不等於1時候代表重複2.
  • 這個Excel求和公式太牛了,1秒搞定一個月的數據匯總!
    老師曾經推過一期跨表公式合集，其中有一個是利用sum進行多表求和。【例】如下圖所示，需要在匯總表中統計1~30日的各個商品銷量合計（日報表和匯總表格式、位置完全一樣）B2)有同學提問：如果各個表中商品的位置（所在行數）不一樣，該怎麼求和？老師今天要分享一個更強大的支持行數不同的求和公式。分析及公式設置過程：如果對單個表（比如1日）進行對A商品進行求和，可以直接用sumif函數搞定：1日表
  • 不學會這3個Excel隔列求和函數公式,你只能一個個單元格去相加!
    Excel中的求和，並不是你們想的1+2=3那麼簡單。有單條件求和、多條件求和，合併單元格求和，隔列求和等等。有關求和的函數也很多，例如SUM函數、SUMIFS函數、還有與這兩個函數結合使用的其它函數等等，不知道你們了解了多少。今天重點跟大家講如何隔列求和，下面3個公式，如果你認真學，隔列求和對你來說就很easy了。
  • Excel中多個工作表不同位置數據,如何進行求和?
    最近收到一個典型案例，Excel中多個工作表不同位置數據，如何進行求和。具體如下：一個Excel文件中，有多個sheet工作表，「一月」，「二月」，「三月」。由於A列數據不重複，因此sumif函數就表示查找後的結果一列數組求和使用函數sumproduct即可求和根據上述思路，直接輸入函數公式即可求出結果=SUMPRODUCT(SUMIF(INDIRECT({"一月","二月","三月
  • 10個必會的Excel求和公式,你掌握多少?
    大家好，我是你們的小可~還記得此前小可講過的求和小技巧嘛！老闆要求那麼多~只會幾個小技巧怎麼行！今天給大家講下打工人必會的求和公式！求和函數SUM是大家再熟悉不過的函數之一了，使用SUM進行累計求和的方法如下：在C2單元格輸入公式：
  • 6個常用Excel公式超全解析,直接套用工作效率翻倍,收藏備用
    上班族在日常工作中都會用到Excel，在使用Excel的過程中很多人都表示函數非要實用，但是真的太難了！稍微一個字符出錯都不可以。你在用函數的時候還在慢慢地上網查詢嗎？下面為大家整理了職場最常用的10個函數，建議收藏哦！1.
  • Excel:提取、比較兩列中的重複值
    今天就介紹一下用COUNTIF函數來提取兩列數據之間的重複值。首先來看一下COUNTIF函數的語法。Excel中COUNTIF函數使用率較高，它用來求滿足區域內指定條件的計數函數，基本語法是：countif（range，criteria）range表示要計算其中非空單元格數目的區域criteria表示統計條件下面看一個例子，我們要統計下表中同時參加英語和日語培訓的名單。
  • Excel在教學上的常用函數,值得你擁有!
    Excel在教學上的常用函數，值得你擁有哦！
  • 學一個高手也看不懂的Excel公式
    如果你認為Excel函數公式學的差不多了，蘭色想讓你看一個Excel公式：=SUMIF(明細表[商品名稱],A2,明細表[銷售數量
  • Excel表格求和,為什麼總差幾分錢?
    在excel中有個常識一定要知道：設置單元格數字格式只是讓你看的，並不能改變單元格的值。
  • 15個Excel常用函數公式(2020年3月)
    為了方便同學們查找excel函數公式，蘭色今天再分享一批常用的函數公式，希望能對大家有用。
  • Excel 排除重複值計算個數的7個公式(趕緊收藏)!
    SUMPRODUCT將上述數組元素求和，即是不重複姓名的個數。公式：{=SUM(1/COUNTIF(B2:B26,B2:B26))}原理和第一個公式相同，只不過用數組運算方式，所以用<Ctrl+Shfit+Enter>三鍵結束。
  • 這個Excel求和公式太牛了
    前幾天蘭色推過一期跨表公式合集，其中有一個是利用sum進行多表求和【例】如下圖所示，需要在匯總表中統計1~30日的各個商品銷量合計
  • 常用Excel排名公式大全,再不收藏就是你的錯了!
    但遇到不同的表格需要用不同的函數和公式，今天蘭色首次對排名公式進行一次全面的總結，同學們一定要收藏起來了！>2、中國式排名有相同數字時，下一個排名連續而不間隔（如下圖中E6單元格）=SUMPRODUCT(($C$3:$C$12>=C3)*(1/COUNTIF(C$3:C$12,C$3:C$12)))
  • 學一個Excel超級表公式
    如果你認為Excel函數公式學的差不多了，蘭色想讓你看一個Excel公式：=SUMIF(明細表[商品名稱],