+ Reply to Thread
Results 1 to 15 of 15

I need help with sumproduct and countif

  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Question I need help with sumproduct and countif

    Hi,

    I am breaking my head to find an formulae to solve this and I am unable to figure out. Can someone please help me.

    I have an report that has an entry sheet where the employees enter the loan numbers that they have worked on in column "C" then the borrower name in "D" Loan validation (Pass or Fail) in G. columns H to Q have failed items that refers to a code like 19-B, 3-A which basically says (19) means which document had error and (B) means what was the error. like name in application form etc.

    the loan number has 10 digits, the first 3 digits refer to the branch code where they are originated. Branches are gouped together as Teams so we have team 100, 200 and 300, with each team having 50 branches each branch has a unique 3 digit code that will be the first 3 digit of the loan number.

    my manager aksed me to map the entry sheet to another sheet in suach a way that it should reflect total number of loan processed for each Team (100, 200 and 300 seperatley), and then it should also show how many loans failed the validation for each Team. He also wants me to show the total number of errors for each team.

    I am realy not sure how will I add multiple numbers (branch codes) and then count the total. I am unable to find a solution for all three above requirements. Any help is really appriciated.

    Thanks
    Jaya

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: I need help with sumproduct and countif

    Hi Jaya,

    Welcome to the forum.

    I believe you can use the CountIFs or Sumproduct as you have mentioned, in this query. Could you upload as sample workbook?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Question Re: I need help with sumproduct and countif

    Here is the attachment.

    Thanks
    Jaya
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    Can some one please help??? I need to submit the report by EOD tomorrow.. please..

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: I need help with sumproduct and countif

    Hi Jaya..

    How one will identify in New Request sheet that which data row belongs to which team ?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    Hi,

    They wont identitfy the team in new request they will just enter the loan number. what I thought was I can extract the numbers usuing =SUMPRODUCT(--(LEFT('New Request'!C12:C150,3)="XYZ"), where XYZ defines the first 3 digits of the loan number. this will show the total number of loans for each branch code. them will sumup the loans for the team 100, 200 and 400. that gives solution to the total files. But this will need lot of formulaes as I need to count the loans using formulae each branch code.

    coming to the second one Doc failures this need to show count the numbers for each branch using the branch code but has to count only the failed validation loans. hmmm not sure how to do this...

    same with the flags it need to count the failures only for that perticular branch.

    Jaya

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: I need help with sumproduct and countif

    them will sumup the loans for the team 100, 200 and 400.
    How? where you are identifying which data belongs to team 100/200 etc.. ?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  8. #8
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    I am unable to upload the excel sheet as it doesnt allow me to exceed 1000KB. shall I send it to your email?

  9. #9
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    Its in the final report sheet where I have shows team and branch numbers. So in the next row I will put =SUMPRODUCT(--(LEFT('New Request'!C12:C150,3)="225")). This will count the loans from the New Request sheet.

    Team Branch
    100 225
    100 226
    100 227
    100 228
    100 229
    100 230
    100 233
    100 234

    Thanks

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: I need help with sumproduct and countif

    Hi Jaya,

    Suggest you to have only few rows of data which will reduce the size of the workbook and then try uploading.. thanks
    Make sure you have highlighted all the references which would be used to calculate any result.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  11. #11
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Question Re: I need help with sumproduct and countif

    See attached.

    I have defined the teams and also have put the formules to count in final report sheet.

    Thanks
    Jaya
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    Can someone help me with this please??

  13. #13
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    help please!!!

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need help with sumproduct and countif

    If I understand correctly, in C3:

    =SUMPRODUCT(--(LEFT('New Request'!$C$12:$C$150+0)*100=100))

    in D3:

    =SUMPRODUCT(--(LEFT('New Request'!$C$12:$C$150+0)*100=100),--('New Request'!$G$12:$G$150="FAIL"))

    in

    E3:

    =SUMPRODUCT((LEFT('New Request'!$C$12:$C$150+0)*100=100)*('New Request'!$G$12:$G$150="FAIL")*('New Request'!H12:Q150<>""))

    same formulas in other boxes, only changing the =100 to =200, =400, respectively.

    I also noticed that you have some cells, D3, E3, etc that are formatted to show white font.. you should change to Automatic to see the number.
    Last edited by NBVC; 06-08-2012 at 11:42 AM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  15. #15
    Registered User
    Join Date
    06-07-2012
    Location
    Mangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need help with sumproduct and countif

    Thanks a lot!!! Its working...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1