Solved

VLOOKUP Calculation

Posted on 2013-01-23
7
281 Views
Last Modified: 2013-01-23
Hello,

I am trying to perform a lookup where I want to find the reference in column A and total the amounts in columns B or C. As you will see in my attached sample, I have a list of utilities in column A. In column B I have the cost and in Column C I have the amount paid.

In Column E I want to get the total cost for Electric and in Column F I want the total paid.

How do I find and total up all occurrences of Electric.

Thanks,

John
Util-Sample.xlsx
0
Comment
Question by:jpldpd
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38811277
That would be the function dsum and not vlookup
0
 
LVL 24

Expert Comment

by:Steve
ID: 38811278
you can just use SUMIF... see attachment 1.

or you can use SUMIFS on normalised data... see attachment 2.
Util-Sample.xlsx
Util-Sample.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38811328
Try:

=SUMIFS(B$2:B$9,$A$2:$A$9,$E$1)

copied to next column
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:jpldpd
ID: 38811354
Hello Barman,

Thanks! One last question,  what if I wanted to include a date and look for all Utilities within a date range? For example I want a total of all bills from 01/01/2013 through 01/31/2013. Would that sill be a SUMIF?

Thanks,

John
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 38811432
to do between dates would for certain need to be SUMIFS

so like the attached
Util-Sample.xlsx
0
 

Author Comment

by:jpldpd
ID: 38811487
Hello Barman,

Thanks!

John
0
 
LVL 24

Expert Comment

by:Steve
ID: 38811763
You are welcome John,

ATB
Steve.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel format formula for currency 15 24
Excel Cell Total 3 20
Return Column Number based on a specific value 25 26
EXCEL formual to calculate Quarter 6 37
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question