Worksheet Count Function Help

[94mustang]

Excel Worksheet Function Experts,

The table below is an example of what I am trying to COUNT. When a single project (Project1 below) has a Work Item that is “Process Complete” AND Proj Status is “Completed” AND Work Item is “Logo Returned” AND Proj Status for the Logo Returned is “Canceled” OR “Completed”, how do I get it to count as a single value of 1? Other Software is generating this table of data and I need to use one or multiple functions to develop a table that will provide a total number of projects that have been completely manufactured. In other words, when I have the conditions in blue text met for a single project, I would like to count this as a value of 1 so the formula will total the number of projects. Project3 is another example but may not have the “Canceled” portion for Logo Returned in the Proj Status column. Any help would certainly be greatly appreciated.

Can this formula be made into one cell to calculate a total? I would prefer not to have a formula in every cell next to the row if at all possible. However, if formulas are needed on every row, I am okay with that too.

Proj Name<o></o>	Work Item<o></o>	Proj Status<o></o>
Project1<o></o>	Process Complete<o></o>	Completed<o></o>
Project2<o></o>	Shipped<o></o>	Cancelled<o></o>
Project3<o></o>	Logo Returned<o></o>	Completed<o></o>
Project2<o></o>	Issued<o></o>	Not Started<o></o>
Project3<o></o>	Process Complete<o></o>	Completed<o></o>
Project1<o></o>	Logo Returned<o></o>	Canceled<o></o>
Project1<o></o>	Logo Returned<o></o>	Completed<o></o>

<TBODY>
</TBODY>

<o></o>

 

[94mustang]

XOR LX,

I did test on Friday and even dissected it myself so I could understand what it was doing but did not have much success. How would the formula still detect a count of three (3) if on Project6 for Logo Returned was Canceled? The current formula is detecting a count of only two (2). I think after trying to dissect this formula for myself, I think I see a little of what it is going on but not enough to make any changes to the formula. The software that imports this data can be in any order or even random. Does this affect the formula you provided? I think after reviewing this formula, I have made some modifications to hopefully help in a better explanation.

For those projects that have a Work Item = Process Completed with corresponding Project Status = Completed only count those which correspond with Work Item = Logo Returned with corresponding Project Status as Canceled OR Completed.

The table below I think would a better illustration.

Proj Name	Work Item	Project Status	Work Item	Project Status	Count
Project1	Process Completed	Completed	Logo Returned	Canceled	1
Project2	Process Completed	Completed	Logo Returned	Completed	1

<tbody>
</tbody>

After looking at my previous posts, I see that I did make mistakes in communicating what I am looking for. I apologize for that mistake. The logic should be of those projects that have Process Completed, Completed, which ones have Logo Returned as either Canceled OR Completed. There would not be a situation where Logo Returned is both Canceled and Completed for the same project.

I hope this helps better communicate what I am looking for. Thanks.

 

[XOR LX]

Hi,

I'm not sure to which data you're referring. Using that as given by you in post #7, the formula generates a result of 4.

I think it would be useful if you could relay your findings re that data, and perhaps we should also use it - and variations of it - for all future discussions.

The order of the data does not affect the result of the formula.

Edit: if you're wondering why I get 4, and not 3 as you stated should be the case, it's since I believe that you missed Project2 in your colouring, which also satisfies the criteria you give.

Regards

 

[94mustang]

XOR LX,

Thanks for your observations as it seems you know the data better than I do. Did post #11 change anything for the formula? Did my post #11 better explain my situation? Can you help me understand your formula a little better as I like what you have did. I did review arrays and entering array formulas. I know how to use the COUNTIFS function but not so much of the MMULT for multiplying arrays. I did research it to try and figure it out but you maybe able to explain it better. I think from the table I created in post #7 and from my post #11, I have explained any situation. If you did go back to my data table in post #7 and changed the ones where Logo is completed and canceled for the same project and changed one of them to something else besides what the formula is looking for, does it affect the count? I have not done this as I am still trying to understand the formula. I think it is great and I want to understand more Excel functions especially arrays as I can see the power in using them. Thanks so much for your patience and helping me through this issue I have. Look forward to a breakdown explanation of the formula.

 

[XOR LX]

I'd be happy to explain the technical aspects of the formula to you, though I feel that this would be best done after you have confirmed that the formula is actually giving you the results you require, tested against different permutations of your dataset, which I feel you have not yet done.

I am not sure why you say that you "have not done this" - surely you (not to mention I) want to be certain that the formula actually works in all situations, before potentially dissecting it?

Note that at no point have I stated that this is an array formula (i.e. needs to be confirmed with CTRL+SHIFT+ENTER).

Regards

 

[94mustang]

XOR LX,

The reason I said, “have not done this” is because when I did test it the first time by changing different data sets, I did not realize I had additional data in the table from post #7 which was not highlighted but being counted in the formula. You helped me see this in a later post. Again, it was my mistake. After making those corrections, I find the formula working just fine. Before your last post, I created a completely different table of data with different variations and found the formula is working just fine and counting correctly. I did that just this morning.

I appreciate you pointing out the mistakes in my posts because it will only help me get better in the future.

I have included a new table below and the count is four (4) when I visually search the table and your formula result is four (4) so I am now confident that this formula will do the job. I must say excellent work. By all means, please help explain to the student.

Count	Proj Name	Work Item	Project Status
1	Project1	Process Completed	Completed
	Project3	Shipped	Canceled
	Project5	Logo Returned	Completed
2	Project2	Process Completed	Completed
	Project1	Logo Returned	Late
	Project4	Delivery Complete	Late
3	Project2	Logo Returned	Canceled
4	Project1	Logo Returned	Completed
	Project9	Process Completed	Completed
5	Project12	Logo Returned	Canceled
	Project4	Installation Complete	Proposed
6	Project6	Process Completed	Completed
	Project5	Logo Returned	Needs Attention
	Project1	Ship Date	Needs Attention
	Project5	Installation Complete	Not Started
7	Project6	Logo Returned	Canceled
	Project3	Installation Complete	Not Started
8	Project12	Process Completed	Completed
	Project8	Process Completed	Late
	Project9	Logo Returned	Needs Attention

<tbody>
</tbody>

 

[XOR LX]

Good to hear!

Actually, I've just re-read part of one of your recent posts which I'd missed before and see that you now say:

There would not be a situation where Logo Returned is both Canceled and Completed for the same project.

but your table in post #7 featured two incidents where this was in fact the case (for Project1 and Project5). This was actually one of the trickier points to deal with, and the formula can be simplified slightly if in fact this is not possible, as you now say.

Can you confirm?

Regards

 

[94mustang]

J.Ty.,

Just wanted to let you know that the COUNTIFS function is not providing the results I am looking for. In my original post, take out the last row in the table. The result should be two (2). I have found out recently that was a mistake I made in the post. I am working with someone that was able to produce the results but I want to get back to you and let you know that the COUNTIFS function is a part of the formula that is needed but not all inclusive. Thanks for your input and appreciate your willingness to help me.

 

[94mustang]

XOR LX,

Yes, it is confirmed. For a project that is Process Complete, Completed, there would not be Logo Returned as being both Canceled and Returned. There is only one choice for each project for the Project Status of Logo Returned and it will be either Canceled OR Completed but not both. I am glad to read that the formula can be simplified. I'm ready for it to be explained. I would think it being simplified, I will be able to understand the longer version of the formula you created as well. Again, I appreciate your patience and time in helping me with this problem.

 

[XOR LX]

Thanks, and apologies if I didn't express myself properly, but that's not quite what I meant, since from your wording that seems to only apply to cases where a given project is Process Complete/Completed.

What I need to know is: is there a possibility of having - at any given time - two lines for a given project, one of which is Logo Returned/Completed, the other of which is Logo Returned/Cancelled, whatever the Work Item status may be?

Regards

 

[94mustang]

I think we are saying the same thing. It's just in different ways. Here are the only two scenarios that you would have for a single project:

Process Complete, Completed, Logo Returned, Completed OR
Process Complete, Completed, Logo Returned, Canceled

If these scenarios show in a table of data, this would be a count of two (2).

Does this help answer the question?