跳到主要內容

如何在Excel中對行或列中的匹配項進行vlookup和求和?

使用vlookup和求和功能可以幫助您快速找出指定的條件並同時求和相應的值。 在本文中,我們將向您展示兩種方法來vlookup並求和Excel中行或列中第一個或所有匹配的值。

Vlookup和總和與公式中的一行或多行匹配
Vlookup和求和項在具有公式的列中匹配
使用出色的工具輕鬆地對行或列中的vlookup和求和進行匹配

有關VLOOKUP的更多教程...


Vlookup和總和與公式中的一行或多行匹配

本節中的公式可以幫助根據Excel中的特定條件將一行或多行中的第一個或所有匹配值相加。 請執行以下操作。

Vlookup並將行中的第一個匹配值求和

假設您有一個水果表,如下面的屏幕快照所示,您需要查找表中的第一個Apple,然後將同一行中的所有對應值相加。 為此,請執行以下操作。

1.選擇一個空白單元格以輸出結果,這裡我選擇單元格B10。 將以下公式複製到其中,然後按 按Ctrl + 轉移 + Enter 獲得結果的鍵。

=SUM(VLOOKUP(A10, $A$2:$F$7, {2,3,4,5,6}, FALSE))

筆記:

  • A10 是包含您要查找的值的單元格;
  • $ A $ 2:$ F $ 7 是包含查找值和匹配值的數據表範圍(不包含標題);
  • 數量 2,3,4,5,6 {} 表示結果值列以表格的第二列開始,並以表格的第六列結束。 如果結果列數大於6,請將{2,3,4,5,6}更改為{2,3,4,5,6,7,8,9…。}。
Vlookup並對多行中所有匹配的值求和

上面的公式只能對第一個匹配值的行中的值求和。 如果要返回多行中所有匹配項的總和,請執行以下操作。

1.選擇一個空白單元格(在這種情況下,我選擇單元格B10),將以下公式複製到其中,然後按 Enter 獲得結果的關鍵。

=SUMPRODUCT((A2:A7=A10)*B2:F7)

在Excel中的行或列中輕鬆進行vlookup和求和匹配:

查找和總和 的效用 Excel的Kutools 可以幫助您快速查看和查找Excel中行或列中的匹配項,如下面的演示所示。
立即下載Kutools for Excel的全功能30天免費試用版!


具有公式的列中的Vlookup和總和匹配值

本節提供了一個公式,用於根據特定條件在Excel中返回列的總和。 如下面的屏幕截圖所示,您正在水果表中查找列標題“ Jan”,然後對整個列值求和。 請執行以下操作。

1.選擇一個空白單元格,將以下公式複製到其中,然後按 Enter 獲得結果的關鍵。

=SUM(INDEX(B2:F7,0,MATCH(A10,B1:F1,0)))


使用出色的工具輕鬆地對行或列中的vlookup和求和進行匹配

如果您不擅長應用配方,請在此處推薦您 Vlookup和總和 的特點 Excel的Kutools。 使用此功能,您只需單擊一下即可輕鬆查找和匯總行或列中的匹配項。

申請前 Excel的Kutools首先下載並安裝.

Vlookup並對一行或多行中的第一個或所有匹配值求和

1。 點擊 庫工具 > 超級查找 > 查找和總和 啟用該功能。 看截圖:

2。 在裡面 查找和總和 對話框,請進行以下配置。

  • 2.1)在 查找和總和類型 部分,選擇 在行中查找和總和匹配值 選項;
  • 2.2)在 查詢值 框,選擇包含您要查找的值的單元格;
  • 2.3)在 輸出範圍 框,選擇一個單元格以輸出結果;
  • 2.4)在 數據表範圍 框,選擇不包含列標題的表範圍;
  • 2.5)在 選項 部分,如果您只想對第一個匹配的值求和,請選擇 返回第一個匹配值的總和 選項。 如果要對所有匹配項的值求和,請選擇 返回所有匹配值的總和 選項;
  • 2.6)點擊 OK 按鈕立即獲得結果。 看截圖:

備註:如果您要進行vlookup並彙總一列或多列中的第一個或所有匹配值,請檢查 在列中查找和總和匹配值 對話框中的“選項”,然後配置為如下所示的屏幕截圖。

有關此功能的更多詳細信息, 請點擊這裡.

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


相關文章

跨多個工作表的Vlookup值
您可以應用vlookup函數以返回工作表中的匹配值。 但是,如果您需要跨多個工作表vlookup值,該怎麼辦? 本文提供了詳細的步驟來幫助您輕鬆解決問題。

Vlookup並在多列中返回匹配的值
通常,應用Vlookup函數只能從一列返回匹配的值。 有時,您可能需要根據條件從多個列中提取匹配的值。 這是您的解決方案。

Vlookup在一個單元格中返回多個值
通常,在應用VLOOKUP函數時,如果存在多個符合條件的值,則只能獲取第一個的結果。 如果要返回所有匹配的結果並將它們全部顯示在一個單元格中,如何實現?

Vlookup並返回匹配值的整個行
通常,使用vlookup函數只能返回同一行中特定列的結果。 本文將向您展示如何根據特定條件返回整行數據。

向後Vlookup或反向
通常,VLOOKUP函數在數組表中從左到右搜索值,並且它要求查找值必須位於目標值的左側。 但是,有時您可能知道目標值,並想找出相反的查找值。 因此,您需要在Excel中向後vlookup。 本文中有幾種方法可以輕鬆解決此問題!

有關VLOOKUP的更多教程...

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件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 (26)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I need help with a formula. I have 2 sheets to pull data from and calculate the sum of on column with a match to a name.
I.e Name is in sheet 1 D5, the name in sheet 2 is B3 the amounts to calculate which match the name in B3 in sheet 2 is F3 to F32.
I've tried sum index, vlookup, match and so on but keep getting a 0. Can anyone assist with this?
This comment was minimized by the moderator on the site
Hi Marnel Strydom,
I don't know if I understand you correctly: If the name in D5 of Sheet1 matches the name in B3 of Sheet2, then sum numbers in the range F3:F32 of Sheet2.
If so, you can apply the following formula to get it done.
=SUM(INDEX(Sheet2!F3:F32,0,MATCH(Sheet1!D5,Sheet2!B3,0)))
This comment was minimized by the moderator on the site
I have a multi sheet spread sheet keeping track of job hours. I have used VLOOKUP in succession to sum all the hours on multiple sheets and it works great... Until it gets to a sheet that does not contain the lookup value. I have searched all over for my issue, and VLOOKUP may be the incorrect solution. I was wondering if I could rattle anyone's brain to make this work.

I.E. I have 1 excel document with 52 tabs. Each tab is a work week starting from January so WW1 is all the hours FOR sed jobs I did for that week. "joes house 2 hours ; mikes house 3 hours"... WW2, WW3 etc... Until WW52.

This is the function I made to add hours together...

=SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE)) And it works great. But when that job is finished it is not on (for example WW32 tab). Hence I get the #N/A error. so for example, as the previous one works great when I expand the formula to cover all 52 sheets... (EXAMPLE OF NEXT PAGE WIOTHOUT LOOKUP VALUE)

=SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE)) I get the #N/A error because the job is not listed on WW32. But I may add hours to that on WW45.

Is there a way to make VLOOKUP skip a sheet that does not have the referenced value and continue summing it till the end? I apologize, this may be as clear as mud but I will clarify anything if need be.

I have also tried IFERROR. You can set IFERROR to return text or even blanks, but does not seem to cover summing. I'm looking for how to SUM multiple sheets when some of the sheets do not contain the lookup value. When using IFERROR function, instead of RETURNING #N/A it just returns "YOU'VE ENTERERED TOO MANY ARGUMENTS FOR THIS FUNCTION"...

=IFERROR(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE),"")

And that's just 3 sheets.

Any help would be greatly appreciated.

P.S. I have tried with CTRL+SHIFT+ENTER as well to no avail.
This comment was minimized by the moderator on the site
Hi Joe,
The methods provided in the following article can do you a favor. Please give it a try. Hope I can help.
How To Vlookup Across Multiple Sheets And Sum Results In Excel?
This comment was minimized by the moderator on the site
A B C D E F
1 I want this cells in col B to sum the values in col F7 (today) to say F20. this will reduce as tomorrow it will sum the values from F8:F20 and so on -$19 I
want this cells in column B to sum the values in col F7 (today) to say F10.
this will reduce as tomorrow it will sum the values from F8:F10 and so on

2 Fri 22 Jul 22 -$19
3 Sat 23 Jul 22 -$19
4 Sun 24 Jul 22 -$19
5 Mon 25 Jul 22 -$19
6 Tue 26 Jul 22 -$19
7 Wed 27 Jul 22 -$19 tried with vlookup, needless to say it doesn't work
8 Thu 28 Jul 22 -$19 =L8=(D1-E1)+SUM(vlookup(today(),6,false):F28)
9 Fri 29 Jul 22 -$19
10 Sat 30 Jul 22 -$19
11 Sun 31 Jul 22 -$19
12 Mon 01 Aug 22 -$19
13 Tue 02 Aug 22 -$19
14 Wed 03 Aug 22 -$19
15 Thu 04 Aug 22 -$19
This comment was minimized by the moderator on the site
hi
How can I add multiple vlookups together and sum it up ?


=VLOOKUP(E3,'Waste Process NEW'!N:O,2,FALSE) --------------- for this everything goes fine except that retune valve is only the first one where N column have many values match's lookup values of E3.


using index didn't help and shows #N/A
=SUM(INDEX('Waste Process NEW'!N:N,0,MATCH('Monthly Report'!H5,'Waste Process NEW'!1:1048576,0)))
This comment was minimized by the moderator on the site
Hi tariq,
Would you mind providing a screenshot of your data? Sorry for the inconvenience.
This comment was minimized by the moderator on the site
I just get a #VALUE! when I try to do all matched values, but it works for the first value. Any idea?
This comment was minimized by the moderator on the site
How can I add multiple vlookups together, for example I want to look up from your example Apples + oranges + bananas for January.Is there a way to do this?
This comment was minimized by the moderator on the site
I'm having trouble with a similar formula I feel like I need to use an index but can't figure it out. I'm trying to get the sum but the lookup is based on 2 values. Column D which contains an employee's extension and column I which provides a logout code. The goal is to find the sum of time in column K based on a specific employee (column D) and the logout code (column I).
This comment was minimized by the moderator on the site
Hi Mike,
Do you mind uploading a screenshot of your data?
This comment was minimized by the moderator on the site
Thanks but I was able to get my formula up and running with a sumifs calculation. Sumifs giving the sum of something based on multiple factors. Mine being an employee ID number and a status code.
This comment was minimized by the moderator on the site
what will be the formula to add qty of apple in jan only
This comment was minimized by the moderator on the site
the =SUM(PRODUCT((A2:A7=A11)*B2:I7) is not working with decimal point.
This comment was minimized by the moderator on the site
For me works just fine. Try without separating SUM and Product, it should be =SUMPRODUCT((A2:A7=A11)*B2:I7)
This comment was minimized by the moderator on the site
can one sum things up in a column and not a row?
This comment was minimized by the moderator on the site
Hi Jelly,
This formula =SUM(INDEX(B2:F9,0,MATCH(A12,B1:F1,0))) can help you solve the problem. Please have a try. Hope I can help.
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