Solved

VLOOKUP Calculation

Posted on 2013-01-23
7
269 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 97
copy the content of word to outlook 3 48
text replacement instead of two macros only one macro 2 20
Easy Excel formula needed 4 26
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now