Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
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]
Re: Return Total value from one sheet to another sheet depend on criteria ?
Thank you, works perfectly.
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.
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"
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
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
Re: Return Total value from one sheet to another sheet depend on criteria ?
Hello,
Attached is your workbook ...revised ... the two formulas ...:wink:
Hope this will help
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"}))
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
Pending Formula
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
[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 ...
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"))
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"}))
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]
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.
Don’t have an account yet? Register yourself now and be a part of our community!