Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 5
Default SumIf and Vlookup together?

I'm having trouble determining what formula I need, maybe vlookup and sumif together? I've never done that before. I need to get the sum of two numbers in a different tabs but have some criterea. The situation is that a vendor can either pay via check or cash each month. My summary tab as well as my other tab that has the info have all the vendors on them (spelled the same). My vlookup would be by vendor, but there could be payments for 2011 or 2012 (year is a seperate column). I want to sum all 2012 payments (cash and check) by vendor. I don't want to create a pivot table as I want this to auto calculate.

Tab Vendor Year Check Cash
January Smith Company 2012 $100 $200
February Smith Company 2011 $300 $50
March Smith Company 2012 $200 $200

The total on the summary tab for Smith Company for all 2012 payments for the months of Jan - Mar would be $700.

Thanks
Chris
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by cmillls45 View Post
I'm having trouble determining what formula I need, maybe vlookup and sumif together? I've never done that before. I need to get the sum of two numbers in a different tabs but have some criterea. The situation is that a vendor can either pay via check or cash each month. My summary tab as well as my other tab that has the info have all the vendors on them (spelled the same). My vlookup would be by vendor, but there could be payments for 2011 or 2012 (year is a seperate column). I want to sum all 2012 payments (cash and check) by vendor. I don't want to create a pivot table as I want this to auto calculate.

Tab Vendor Year Check Cash
January Smith Company 2012 $100 $200
February Smith Company 2011 $300 $50
March Smith Company 2012 $200 $200

The total on the summary tab for Smith Company for all 2012 payments for the months of Jan - Mar would be $700.

Thanks
Chris
Depending on which version of Excel you're running, you can do this with SUMPRODUCT or SUMIFS.

Although as you have a worksheet for each month a summary worksheet would make things easier.

Happy to help you set this up if you can provide an example workbook.

Last edited by Spencer101 : September 14th 12 at 04:26 PM
  #3   Report Post  
Junior Member
 
Posts: 5
Default

Quote:
Originally Posted by Spencer101 View Post
Depending on which version of Excel you're running, you can do this with SUMPRODUCT or SUMIFS.

Although as you have a worksheet for each month a summary worksheet would make things easier.

Happy to help you set this up if you can provide an example workbook.
I've got 2010. Here's the file, hopefully the attachment worked. I had to do a zip.
Attached Files
File Type: zip Book1.zip (13.3 KB, 165 views)
  #4   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by cmillls45 View Post
I've got 2010. Here's the file, hopefully the attachment worked. I had to do a zip.
In your sample workbook would Smith (for example) have figures for multiple years on the Jan worksheet? So a 2011 value and a 2012 value?
  #5   Report Post  
Junior Member
 
Posts: 5
Default

Quote:
Originally Posted by Spencer101 View Post
In your sample workbook would Smith (for example) have figures for multiple years on the Jan worksheet? So a 2011 value and a 2012 value?
I should have been more clear. No each tab would only have one year per vendor.


  #6   Report Post  
Junior Member
 
Posts: 5
Default

Can anyone think of a solution?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif and vlookup Help Nikki Excel Discussion (Misc queries) 2 June 3rd 10 06:54 PM
Vlookup + sumif , but how???? Jessejames Excel Worksheet Functions 4 April 21st 10 06:41 PM
sumif/vlookup Alberta Rose Excel Programming 1 May 13th 09 01:41 AM
sumif or vlookup help Katie Excel Worksheet Functions 2 July 2nd 08 05:21 PM
Vlookup vs Sumif Steph Excel Programming 4 July 31st 06 09:32 PM


All times are GMT +1. The time now is 06:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"