Dear All,
basically I have two questions, details in my excel sheet:
- why does my vlookup not work in the sheet
- how can I combine vlookup (or hlookup) with selected sums
ExplanationOfProblem.xlsx
it would be great if you could help me to solve this with a formula, rather than PIVOT or changing the layout a lot. there is quite a large original file and I have not many options to change the layout..
thanks a lot!
kind regards,
Sandra
Hi Sandra,
If you are looking for summations based on certain criteria, then SUMIFS would be a better function choice rather than VLOOKUP.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
hi!
many t hanks for the hint, seems that lookups are not necessary then..
I just still have troubles. I know how to do a basic sumif which links to other cells (=SUMIF(H5:H10,TRUE,Sheet2!C8:H8). however, here I have the problem that I have
- several events -> and I want to show the sum per event (in column D on sheet 1) -> and I want this sum to only include the selected companies.
Sorry, might be very basic for you but I just can't find out how to do it
many thanks!
kind regards,
Sandra
In your example document, in cell C6 on Sheet1, try this:
The formula for D6 will need to be an array formula:
=SUMIFS(Sheet2!$I$8:$I$13,Sheet2!$A$8:$A$13,A6,Sheet2!$B$8:$B$13,B6)
confirm as an array formula with Ctrl-Shift-Enter.=SUMPRODUCT(INDEX(Sheet2!$C$8:$H$13,MATCH(A6&B6,Sheet2!$A$8:$A$13&Sheet2!$B$8:$B$13,0),0),--TRANSPOSE($H$5:$H$10))
Copy both formulae down.
Cheers,
I know you asked not for an pivot table.
But I think a pivot table is very usefull to this problem.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Dear All,
many thanks for helping out.
@ConneXionLost: Many thanks for your help and for specifying the exact formulas - much appreciated!
@oeldere: thanks for the link, I will look into it. Pivot was something I always tried to avoid but maybe (with a smaller file than the one I have here) I will try to use it
best regards,
Sandra
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks