Solved

Excell formula to find the sum of all cells in column X from a vlookup in column y?

Posted on 2014-01-08
7
448 Views
Last Modified: 2014-01-09
Hi guys,

I have a csv file contains a log of all the print jobs in chronological order.
each row has the user name and the total number of pages printed.

I want to add a column that has the sum total of all the print jobs for that user.

I'm thinking if I could sum all the cells in column X from a vlookup of all the cells in column Y this would get me the result I want, would it not?

I just have no idea how to write it or if those formulas are the right way to go about it. Any suggestions?

I am using Office 2010

Thanks in advance.
AC
0
Comment
Question by:defecta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39767213
You don't need a VLOOKUP, just some SUMIF. See attachment.

HTH,
Dan
test1.xlsx
0
 
LVL 24

Expert Comment

by:Steve
ID: 39767314
For what you want to do I would suggest a PIVOTTABLE.

This will be more useful as it can be sorted alphabetically, grouped by dates, etc etc.

To add a pivot table, select a cell in your data, choose the INSERT tab and The first icon should be PIVOTTABLE.

There are many guides on Pivot Tables on the web such as this...
http://www.experts-exchange.com/VP_1.html
Go to course progress, pivottables for the relevant PivotTable stuff.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39767403
(no points) Pivot table is definitely the way to go.. I was going to suggest an array formula if you're just looking at one user but a pivot would show you everyone as well as giving you the ability to

if one of the user's is say "john" then you're array formula would be
assuming Col A is username and Col B is the number of print jobs

=SUM(IF(A1:A2000="john",B1:B2000,0))

but you need to press CTRL + SHIFT + ENTER after to finish entering the formula

if you've done that right you'll see curly braces

{=SUM(IF(A1:A2000="john",B1:B2000,0))}
0
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 
LVL 24

Expert Comment

by:Steve
ID: 39767718
Rob, ditch the array formula in Excel 2010 for SUMIFS or simply SUMIF for all versions... no benifit from an array formula there.

=SUMIF(A1:A2000,"john",B1:B2000)

=SUMIFS(B1:B2000, A1:A2000,"john")
0
 
LVL 43

Expert Comment

by:Rob
ID: 39767728
Good point Steve, thanks for the heads up :)
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39767771
And so we get back to my initial solution... :)
0
 

Author Closing Comment

by:defecta
ID: 39769694
Thanks DanCraciun and everyone else for their valuble input.

I was making a simple task a lot harded than It needed to be because of a lack of knowledge of what Excel can do. Thanks again for the simple and clear answer and explanation.
0

Featured Post

SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 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