Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Using SUM and VLOOKUP formula together in to fetch data from multiple cells?
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 01-16-2019, 11:22 PM
Bumba Bumba is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 7 32bit Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2010
Novice
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? 		 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default Using SUM and VLOOKUP formula together in to fetch data from multiple cells?
Say I have an excel file which looks like below:





How do I get values of all the columns in below table fields Quarter-1, Quarter-2, Quarter-3, Quarter-4 in one go, where Quarter-1 is the sum of the values from Jan--2018 to Mar--2018 in the above table, Quarter-2 is the sum of the values from Apr--2018 to Jun--2018 etc.

Now, I've written the below formula's in Quarter-1, Quarter-2, Quarter-3, Quarter-4 respectively

Code:
{=SUM(VLOOKUP([@Name],Table1[[#All],[Name]:[Dec--2018]],{2,3,4},0))}

{=SUM(VLOOKUP([@Name],Table1[[#All],[Name]:[Dec--2018]],{5,6,7},0))}

{=SUM(VLOOKUP([@Name],Table1[[#All],[Name]:[Dec--2018]],{8,9,10},0))}

{=SUM(VLOOKUP([@Name],Table1[[#All],[Name]:[Dec--2018]],{11,12,13},0))}
But can I do this using a single formula and not need to change the column index array values in every column?
Attached Files
File Type: xlsx Test.xlsx (13.4 KB, 13 views)
Reply With Quote
  #2  
Old 01-17-2019, 12:23 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 7 64bit Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,948
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Hi
as external links are notoriously unsafe , that not all members can access them, please post your file on the forum. Thank you
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 01-17-2019, 01:19 AM
Bumba Bumba is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 7 32bit Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2010
Novice
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? 		 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default
Quote:
Originally Posted by Pecoflyer View Post
Hi
as external links are notoriously unsafe , that not all members can access them, please post your file on the forum. Thank you
Hi Pecoflyer,
I've attached the sample file and removed the external download link
Reply With Quote
  #4  
Old 01-17-2019, 08:53 AM
xor xor is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 10 Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,103
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
What about the attached?
Attached Files
File Type: xlsx Sum_Quarter.xlsx (15.8 KB, 14 views)
Reply With Quote
  #5  
Old 01-17-2019, 10:42 AM
ArviLaanemets ArviLaanemets is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 8 Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2016
Expert
  
Join Date: May 2017
Posts: 960
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default
Here is a version with single formula (I used OP's 2nd table)
Attached Files
File Type: xlsx SumTest.xlsx (13.6 KB, 12 views)
Reply With Quote
  #6  
Old 01-17-2019, 09:25 PM
Bumba Bumba is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 7 32bit Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2010
Novice
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? 		 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default
Thanks both xor and ArviLaanemets,
Do you mind explaining your respective codes?
Reply With Quote
  #7  
Old 01-17-2019, 10:18 PM
xor xor is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 10 Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,103
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
See explanation in the attached workbook.
Attached Files
File Type: xlsx Sum_Quarter_2.xlsx (41.7 KB, 12 views)
Reply With Quote
  #8  
Old 01-18-2019, 12:31 AM
ArviLaanemets ArviLaanemets is offline Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Windows 8 Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Office 2016
Expert
  
Join Date: May 2017
Posts: 960
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default
Here you go!
Attached Files
File Type: xlsx SumTest.xlsx (15.4 KB, 11 views)
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Keeping the source format when extracting data from multiple cells using Vlookup function praboos2001 Excel 6 11-15-2016 04:40 AM
Need a formula (possibly something like VLOOKUP) that will return multiple results. SilverUnicorn79 Excel 8 08-16-2016 01:34 PM
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Need help with a VLOOKUP formula & two similar, yet non-identical columns of data. mikey386 Excel 1 12-18-2014 01:59 AM
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? VLookup is giving me #N/A error, help with data or formula? ladygogo78 Excel 3 10-22-2012 12:28 AM
Using SUM and VLOOKUP formula together in to fetch data from multiple cells? Multiple VLOOKUP's checking multiple Cells OTPM Excel 11 05-23-2011 11:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:06 AM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft