Hi,
I have created the following formula but it's not working and I'm wondering if someone out there can tell me where I've gone wrong:
=SUMIFS((VLOOKUP((CONCATENATE(A151,E151)),(CONCATENATE(Timesheets!D$1:D$6000,B$1:B$6000))),MAX(Times heets!E$1:E$6000),Timesheets!AV$1:AV$6000)
So what I'm essentially trying to do it have this formula combine info from 2 columns on one sheet and 2 columns on another sheet in the same workbook. If those columns have the same info then I'm looking for the maxiumum value in a 3rd column to narrow down the rows applicable to be added up. Once it has determined that information it is to add the applicable rows in yet another column on the second sheet to return my result. I don't know if that even makes sense the way I've explained it but I am totally confused and getting a little frustrated at this point.
Any help would be GREATLY appreciated!
Try perhaps:
Confirmed with CTRL+SHIFT+ENTER=SUM(IF(A151&E151=Timesheets!D$1:D$6000&B$1:B$6000,IF(MAX(IF(A151&E151=Timesheets!D$1:D$6000&B$1:B$6000,Timesheets!E$1:E$6000))=Timesheets!E$1:E$6000,Timesheets!AV$1:AV$6000)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Ok, I thought it was working however, upon furthur inspection there seems to be something a little off. I just keep getting results of zero returned even though there is data that should be added up.
I am going to continue to play around with it though. At least I'm no longer getting an error message!
Last edited by emackenney; 02-09-2012 at 01:08 PM.
I'd have to see a sample workbook to diagnose any problem... it could be you aren't getting exact matches....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
How can I send that information to you?
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.
You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file.
To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks