Hello Everyone,
I want to use SUMIFS function with a relation of Sheet1 and Sheet2. My aim will sum project estimations in sheet1 if project number is 1, gender is M. But, after these criteria are met, Name C and D have zero estimation in sheet 1. If this problem occurs, Name C and D data come from sheet2 and will be added to the SUMIFS function. Sheet 2 excel file enlarges downward with new information but Sheet 1 stays the same. If someone would help me, I would be very thankful.
one way (if I'm understanding your request) is this...
=IF(SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!A2,Sheet1!$B:$B,Sheet2!B2,Sheet1!$C:$C,Sheet2!C2)=0,SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet2!A2,Sheet2!B:B,B2,Sheet2!C:C,Sheet2!C2),SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!A2,Sheet1!$B:$B,Sheet2!B2,Sheet1!$C:$C,Sheet2!C2))
or slightly differently if you are wanting 1 and M as the criteria...
=IF(SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!A2,Sheet1!$B:$B,1,Sheet1!$C:$C,"M")=0,SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet2!A2,Sheet2!B:B,1,Sheet2!C:C,"M"),SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!A2,Sheet1!$B:$B,1,Sheet1!$C:$C,"M"))
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi,
Thank you for your effort. I tried to use but codes do not work as same as my idea. Maybe my explanation was not very detailed. In a nutshell, I would like to find the sum of 1 and M criteria. My aim is if in sheet 1, names meet 1 and M criteria and not zero, the sum of that certain name will come from sheet 1. But if in sheet 1, names meet 1 and M criteria and zero, the sum of that certain name will come from sheet 2. Totaly I only wanna see the total sum of 1 and M criteria. If it is possible to modify IF functions, I would be very thankful.
It may help if you manually include the results you want in the file so that we will have something to go by when proposing formulas/code.
Either update the file in post #1 and notify us that updates have been made in a new post, or put an updated copy of the file in a new post so that we will get notified.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks