Return Total value from one sheet to another sheet depend on criteria ?

  • Hello,


    I would like to return for example total value of COMP from Manufactured Status Cell in Cars Information Sheet to Summary Sheet, The rest status is the same.


    Output Should Be as Following:


    Total Cars Complete ( COMP): 3

    Total Cars Pending (APPR+INPRG+WAPPR): 12

    Total Canceled Cars(CAN): 3


    Attached workbook for your reference.

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Try these, respecively
    [COLOR="#0000FF"]
    =COUNTIF('Cars Information'!$K$3:$K$17,"COMP")


    =SUMPRODUCT(COUNTIFS('Cars Information'!$K$3:$K$17,{"APPR","INPRG","WAPPR"}))


    =COUNTIF('Cars Information'!$K$3:$K$17,"CAN")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Dear NBVC,


    I tested mention formula over one workbook which I am currently working on it, I try to return total COMP or total pending for each owner.


    I use this formula as u suggest.


    Code
    =COUNTIFS(Sheet1!E:E,"COMP",Sheet1!H:H,"=Mr Anoop Krishna Ravella")


    I use the above code but it does not work, I am telling formula to check for me this owner Anoop how many times does he complete a project and to return that value to Summary page. Why the result is 0.

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Move the = sign


    Sheet1!H:H,="Mr Anoop Krishna Ravella"


    not


    Sheet1!H:H,"=Mr Anoop Krishna Ravella"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Hello,


    In cell G5, you can test following :


    Code
    =SUMPRODUCT((Sheet1!$H$2:$H$500='Total Summary'!F5)*(Sheet1!$E$2:$E$500="COMP"))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    I removed = sign but it didn't work still gave me 0 result.
    The code I used the following

    Code
    =COUNTIFS(Sheet1!E:E,"COMP",Sheet1!H:H,"Mr Anoop Krishna Ravella")


    Carim,


    I tested formula u mention and it worked, So this formula will search by Project Manager Name right in Colmun H and then check WO Status How Many Complete rights ?


    What If I want to do same formula but for non-complete projects. There are those status instead of complete for example :


    - WSCH
    - INPRG
    - APPR


    I try to use same formula but there is syntax error


    Code
    =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500="WSCH","INPRG","APPR"))
  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    I actually edited my previous Post, I meant to say Move not Remove! You must have used the formula before my editing.


    Try this as the modification of Carim's formula, note the curly brackets enclosing the 3 values.


    =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500={"WSCH","INPRG","APPR"}))


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Carim,


    I tested formula for complete it worked fine, but for pending for example if data almost 500 it is working if I changed data to larger rows this is what happen:


    Example:


    Yahya is having 2592 Completed Projects , 86 Pending Projects.


    After using formula with this much of rows I got the following output :


    Completed Projects 92
    Pending Projects 1


    For the Completed I changed value of 500 to 14005, and the output was correct GIVEN 2592 . However for the Pending Projects I try it but it did not gave correct output GIVEN 64 ONLY why ?


    Complete Formula

    Code
    =SUMPRODUCT((Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="COMP"))


    Pending Formula

    Code
    =SUMPRODUCT((Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="WSCH")+(Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="INPRG")+(Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="APPR"))
  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    I tested this formula for Pending Projects and the output was as following after changing the line instead of 500 to 140005.


    Pending Projects 64


    Code
    [COLOR=#0000ff]=SUMPRODUCT((Sheet1!$H$2:$H$140005= 'Total Summary'!F5)*(Sheet1!$E$2:$E$140005={"WSCH","INPRG","APPR"}))

    [/COLOR]

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Hello again,


    May be your database is really huge ...


    If it is the case, you should insert a Pivot table to get your analysis and results ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    The probable reason only 64 Pending is returned is probably because only 3 values are counted whereas there must be other values when all 140,005 rows of data is used.


    Try


    =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500<>"COMP"))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    I tested mention formula and it is working fine, but however the only problem for example if I would have WO Status with "CAN" then this formula also will calculate everything except COMP & it is including CAN Status which I don't want.


    Will it work If I modify it not equal to comp or CAN ?

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Try


    =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500<>{"COMP","CAN"}))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    If it's possible to use COUNTIFS instead of SUMPRODUCT to count occurances it would be more advisable, as it is less expensive in terms of processor usage and will make calculations faster..


    [COLOR="#0000FF"]=COUNTIFS(Sheet1!$H$2:$H$500,'Total Summary'!F5,Sheet1!$E$2:$E$500,"<>COMP",Sheet1!$E$2:$E$500,"<>CAN")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    This formula gave me COMP output
    =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500<>{"COMP","CAN"}))

    NBVC,


    =COUNTIFS(Sheet1!$H$2:$H$500,'Total Summary'!F5,Sheet1!$E$2:$E$500,"<>COMP",Sheet1!$E$2:$E$500,"<>CAN")


    above formula is working, Shall i use same for COMP formula below?

    =COUNTIFS(Sheet1!$H$2:$H$500,'Total Summary'!F5,Sheet1!$E$2:$E$500,"COMP")

  • Re: Return Total value from one sheet to another sheet depend on criteria ?


    Yes. Countifs is more efficient then Sumproduct.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!