• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get result with if and sumif function

B

balkrishna

New Member
Hellow Sir,
I have a work book with 3 Sheets and each sheet have some data. In Sheet 1 i need some data input result of August 14 sheet. I have attached file for your reference. Kindly help.

Point 1. If the person is a Branch Manger Designation wise of E column of Sheet1 then it will check the Total Branch Target of B column of Aug14 sheet not individual wise. If the Department is Business Development F column of Sheet1 then also take the total Branch Target not individual.

Point 2. If the Department will be Credit & Operation F column of Sheet1 then to check Employee Name wise total of F column of Aug 14 sheet but in some cases the Branch Manager’s Department comes under Credit & Operations. In this case we have to take Branch wise target not employee wise.
I need the result in yellow marked cell.
Thanks.
 

Attachments

  • Loan Sheet.xls
    35.5 KB · Views: 2
Formula in I2:
=IFERROR(IF(OR(E2="Branch Manager",F2="Business Development"),SUMIF('Aug 14'!B:B,D2,'Aug 14'!D:D),INDEX('Aug 14'!D:D,MATCH(C2,'Aug 14'!$F$1:$F$100,0))),0)

I'm not sure how Point2 plays in. It seems to be parallel to Point 1 (If not Branch Manager of Business Development, take individual). Point 2 says "If not Branch Manager, for some groups take individual".
 
Sir,

Thanks for your prompt reply.

Actually I am explaining what I want exactly. Sorry I have missed one more point in my previous post. But you can see in the attached sheet. I have put this formula.

1. If in E column have “State Head” or “Zonal Manager” then there is no target and it will get the result “NA”

2. When in E column found “Branch Manager” or “Branch Incharge” then it will not check the department and straight go to “Aug 14 sheet” and pickup the sum of the same Branch.

3. When in F column found “Sales” and “Business Development” then also use the same formula of point 2 of the total branch target.

4. When F column found “Credit” & “Operation” then it will check the total of F column of “Aug 14 sheet” and pickup the sum amount of individual name wise.

5. If “Credit” & “Operation” departments designation will be branch manager then it will check the branch target not individual wise as mentioned in point 4. If this point will not possible then left it. But the rest 4 points we need.
Thanks.
 
Thank you for the additional information. New formula:
=IF(OR(E2={"State Head","Zonal Manager"}),"NA",
IF(OR(E2={"Branch Manager","Branch Incharge"},F2={"Sales","Business Development"}),SUMIF('Aug 14'!B:B,D2,'Aug 14'!D:D),
INDEX('Aug 14'!$D$1:$D$100,MATCH(C2,'Aug 14'!$F$2:$F$100,0))))
 
Hi Sir, Thank you very much. Everything is ok. only one small change is required. In the I column i need the result will be "0" when the Employee Name/Emp code is not in the sheet Aug14. This type of result will be only 3 there is Sheet1. Out of 3 only 2 is ok. Only the 4th row result to be modified. Please help.
 
Last bit with an IFERROR then

=IF(OR(E2={"State Head","Zonal Manager"}),"NA",
IF(OR(E2={"Branch Manager","Branch Incharge"},F2={"Sales","Business Development"}),SUMIF('Aug 14'!B:B,D2,'Aug 14'!D:D),
IFERROR(INDEX('Aug 14'!$D$1:$D$100,MATCH(C2,'Aug 14'!$F$2:$F$100,0)),0)))
 
Sir - Row no 11 & 14 got the result "0" but row no 4, Emp code 12 is not getting Zero which is not in the Aug 14 sheet. Thanks again.
 
But row 4 is Sales, and you said that Sales get SUM of branch. However, I do see now that you have same inidividual listed multiple times in Aug sheet, so switch that part to SUMIF.

=IF(OR(E2={"State Head","Zonal Manager"}),"NA",
IF(OR(E2={"Branch Manager","Branch Incharge"},F2={"Sales","Business Development"}),SUMIF('Aug 14'!B:B,D2,'Aug 14'!D:D),
SUMIF('Aug 14'!F:F,C2,'Aug 14'!D:D)))
 
Hi Mr. Luke, sorry for bother again. I have attached the revised sheet which i am using exact. In this Loan Sheet i need the result of H to J column.
In H column i need the Limit of the Limit Sheet as per the designation of D column and Tier of F column is matched. For Ex. if D2 is Assistant Manager & Tier is Tier 2 then its limit is 750.
In I column i need the Target of the same Limit sheet in the same manner. And the target will be 1500000 after match the Designation and Tier.
An din J column Achieved Column i need the above provided code.
I have attached the revised sheet for your reference. Please Help. Thanks.
 
Hi Balkrishna ,

If you are finding it difficult to upload a file to this forum , use any public file-sharing website , and post the access link here.

Narayan
 
Last edited:
@balkrishna

Which links you have submitted they are requesting to take permission from you

The forum members or Ninja's never take any permission to Help if you want to solve your problem please upload the correct file from Drop Box and speedshare etc and

Thanks
 
Hi balkrishna,

I can't understand your sheet "Limit". If you are listing Targets and Limits lets say in row 2 and 3 for a designation then cities should remain the same. why they are changed for the 3rd row? In second row it is delhi, in third it is mumbai. while designation is deputy manager. any comments on this.
 
In Limit sheet first 2 column i have taken for Tier wise to confirm which sheet will come under which tier. You can see the results in F column in the Loan Sheet. I need H column & I column result. In C column Limit sheet i have mentioned the total designations which mentioned in Master sheet. In Loan sheet D column first check the designation and then the same will match in the Limit sheet C column and then take the result which i have colored in yellow. Again i am attaching the sheet for your reference which i have filled manually. Thanks.
 

Attachments

  • Loan Sheet.xls
    47 KB · Views: 2
@balkrishna -

Use the following formula:
In H2 = INDEX(Limit!$A$2:$G$45,MATCH('Loan Sheet'!$C2,Limit!$A$2:$A$45,0),MATCH('Loan Sheet'!$F2,Limit!$A$1:$F$1,0))

In I2 = INDEX(Limit!$C$2:$F$45,MATCH('Loan Sheet'!$D2,Limit!$C$2:$C$45,0),MATCH('Loan Sheet'!$F2,Limit!$C$1:$F$1,0))

Let me know..if you were looking for something else...
 
Thanks Mr. Ashees for your prompt reply. I have used this code only the current named it is showing result ok. If you change the name or adding records it will not get the exact result. Attaching the sheet with your mentioned code.
 

Attachments

  • Loan Sheet.xls
    57 KB · Views: 4
@balkrishna -
It seems to work fine here unless I misunderstood your requirement...please see the screenshot

If you are looking for something else..Can you please update the result for first 5 entries manually..as a sample...

upload_2014-9-23_8-29-38.png

Questions:

1) Some city names listed in "Limit" tab are missing from "Master Data" tab...?

2) Why does column C of "Limit" tab have designations mentioned for alternate rows while as city names are different?

3) If targets are fixed as per designation and Tier then why don't we put this data in "Master data tab" to manage data in a better way?
 
I need exactly which screenshot. But in Limit sheet C column is no link to A & B column. You can mention A & B column in any other sheet also. i have mentioned only to take Tier by using vlookup. In C column i have mentioned designations in alternative rows because in each designation there is 2 requirement i.e. Target & Limit. You can mention in any format by which i can get result. You may alter anything. But the result will be the same which attached. Thanks.
 
You must log in or register to reply here.
Back
Top