Remember Me?

#1
 Junior Member Posts: 5
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
 Senior Member Posts: 663

Quote:
 Originally Posted by cmillls45 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
 Junior Member Posts: 5

Quote:
 Originally Posted by Spencer101 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
 Book1.zip (13.3 KB, 165 views)
#4
 Senior Member Posts: 663

Quote:
 Originally Posted by cmillls45 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
 Junior Member Posts: 5

Quote:
 Originally Posted by Spencer101 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
 Junior Member Posts: 5

Can anyone think of a solution?
 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Nikki Excel Discussion (Misc queries) 2 June 3rd 10 06:54 PM Jessejames Excel Worksheet Functions 4 April 21st 10 06:41 PM Alberta Rose Excel Programming 1 May 13th 09 01:41 AM Katie Excel Worksheet Functions 2 July 2nd 08 05:21 PM Steph Excel Programming 4 July 31st 06 09:32 PM

All times are GMT +1. The time now is 08:56 PM.