Planning Doc. Totalizer

Hello, I've attached an Excel Sheet that I've created and having trouble resolving the last formula in cell AL17. I have 4 stages of work "Disconnect / Uninstall","Vendor Shop Service","Shop Service / Verify" & "Reinstall / Reconnect " that show the progress of work. I need to track the total of how many jobs are ongoing in cell "AL17 (Total Ongoing)", and I can only attribute a count of 1 per job no matter how many stages of work are ongoing at the same time. Also there are other criterias that would disqualify a job from being counted and those are at the beginning of the formula. I would appreciate any assistance in resolving this. Thank you.

Planning Sheet 1.xlsx

Good morning, thank you for taking the time to respond to my issue and sorry for the delayed reply. My working sheet has a total of 634 rows (jobs) currently only 4 are in progress. Unfortunately when I applied the formula to my sheet it came up with a total of 247 in progress. I don't know if this will help, but I only need to count any one of the stages of work "Disconnect / Uninstall","Vendor Shop Service","Shop Service / Verify" & "Reinstall / Reconnect " that's in progress, so if column "K" is showing a percent between 1-99 then it would get a count and the other columns (P, V & AD) wouldn't need to be checked, but if "K" was showing "", 0% or 100% then the next column "P" would need to be checked and if the percent was between 1-99 the rest of the columns wouldn't need to be checked and so on. Thanks again for looking at this.

Here is an example of the same sheet with different percentages. I'll provide more detail about formula in a bit. I timed out on my last post and everything was erased.

Planning Sheet 2.xlsx

Ok, Planning Sheet 2 examples:

1. Row #3 shows an acceptable value in column "F", 100% complete across all sections and an acceptable value in column "AF", so this row will only receive a count of 1 for work in progress.

2. Row #4-5 shows an acceptable values in column "F", 100% complete in "Disconnect/Uninstall" section, ""(no work) in "Vendor Shop Service" section, and 0% complete in all other sections, and an acceptable values 1.5 in column "AF", so each of these rows will receive a count of 0 for no work in progress.

3. Row #6 shows an unacceptable value(Out) in column "F", 100% complete in "Disconnect/Uninstall" section, ""(no work) in "Vendor Shop Service" section, and 0% complete in all other sections, and an acceptable value 1.5 in column "AF", so each of these rows will receive a count of 0 for no work in progress.

4. Row #11 shows an acceptable values in column "F", 100% complete in "Disconnect/Uninstall" section, 40% in "Vendor Shop Service" section, and 0% in "Shop Service/Verify section, 0% and 100% values in "Reinstall/Reconnect" section, and an acceptable value 6.6 in column "AF", so this row will receive a count of 1 for work in progress.

Thank you for your time and patience. Have a nice day.

Hello, I've attached the completed and working Excel Sheet for you to review. I used the following code:

For each row: =(SUM(COUNTIFS($D15,"X",$F15,"<>OUT",$F15,"<>MERGED",$F15,"<>NONEXISTENT",$F15,"<>OPS TO WORK",$F15,"<>ROUTINE TO WORK",$F15,"<>RELIABILITY TO WORK",$AF15,">0.0")
*IF(AND($K15<100%,$K15>0%),1,IF(AND($P15>0%,$P15<100%),1,IF(AND($V15>0%,$V15<100%),1,IF(AND($AD15>0%,$AD15<100%),1,0))))))

And For cell AL17: =SUM(AO3:AO15)


Planning Sheet 1A.xlsx

There is no specific number that is expected. Think of it as a manufacturing assembly line. The raw material it introduced at the beginning of the process and will go through many steps to become a finished product. What you need to know is how many potential products are at any given step and how many total at any given time. You don't want to count a single product multiple time just because it is going through more than one step hence the row(product) can only have a count of 1 no matter how many steps it in. I hope this helps you to understand what I was trying to achieve.

Yes, the issue has been resolved. No further assistance will be required. Thank you.

Ali, I have always expressed my appreciation for any assistance that I have received. In this thread under post #9 I did just that. The posts after that were to provide to the forum a formula that works in case someone else runs into a similar problem and to clarify what the formula is to do. I did put [SOLVED] on my #11 post which was my mistake and have corrected that. My latest post #16 again expresses my appreciation. Thank you.