Multiple Condition Counts

[Shannon E]

I am hoping someone can help with my formula problems.</SPAN>

I can easily edit a formula to apply to all of the needed items/calculations (a few listed below). I am just including more than one because when I thought I had found the correct formula because it returned 0 when 0 was correct, I discovered that it actually didn’t work because it still returned 0 when I edited it for one of the other scenarios (and the result was supposed to be 5).
</SPAN>
I was using the COUNTIFS function and thought it was working but something is off and I can’t figure out what it is. The link below leads to the file.
</SPAN>
On WIP Aging tab:
</SPAN>
1) ECR Create (UFC) data/formula needs to return the number of rows/items on the ECR Cycle Time – raw tab with a date in the ECR Create column cell, NO date in the ECR Submit column cell and the data in the ECR State column does NOT equal “Cancelled”.
</SPAN>
2) ECR Submit (STCM) data/formula needs to return the number of rows/items on the ECR Cycle Time – raw tab with a date in the ECR Submit column cell, NO date in the ECR Evaluate column cell and the data in the ECR State column does NOT equal “Cancelled”.
</SPAN>
3) ECR Review (CCB) data/formula needs to return the number of rows/items on the ECR Cycle Time – raw tab with a date in the ECR Review column cell, NO date in the ECR Plan ECO column cell and the data in the ECR State column does NOT equal “Cancelled”.</SPAN>

Can anyone help?

Index of /Help

 

[j_unsuitable]

Hey

I am trying to work through all those conditions:
UFC:

=COUNTIFS('ECR Cycle Time - raw'!F2:F115;"<>""";'ECR Cycle Time - raw'!G2:G115;"";'ECR Cycle Time - raw'!C2:C115;"<>Cancelled")

working on STCM right now. You say it should be 5, but if I understood you correctly it should be 4?
There are 5 rows in ECR Evalutate with no date, but for one of those the ECR State is Cancelled?
If it actually should be 4, try this formula:

=COUNTIFS('ECR Cycle Time - raw'!G2:G115;"<>""";'ECR Cycle Time - raw'!H2:H115;"";'ECR Cycle Time - raw'!C2:C115;"<>Cancelled")

Depending on where you live you might have to turn the semicolons into commas!

Let me know if that works for you and if youre able to adapt the formula to the other conditions yourself.

Julian

 

[Shannon E]

Shoot! You are correct, Julian. My apologies - I came up with those numbers before realizing I needed to filter out the "Cancelled" rows.

The formula worked!!

Thank you so much! It was driving me batty trying to figure out what the problem was with my formula.

You are awesome!!

 

[Shannon E]

I do have one other question perhaps you can help with?

The data on the ECR and ECO Cycle Time - raw tabs is from a system-generated report which varies in length. I would like to be able to have the formula setup so it doesn't need to specify a row number. I tried removing the row number references (to be G:G instead of G2:G115, etc.) but that gave me an error. This also happened when I then tried to just make the end row number very high to accommodate a larger report (replaced 115 with 5000), which also errored.

Would you know the correct way to do this?

(And thank you for the note about the commas. I did have to replace the semi colons with commas.)

Shannon

 

[j_unsuitable]

Hey Shannon

I am not sure if thats the right way to do it, but try adding

-COUNTBLANK('ECR Cycle Time - raw'!A:A)

to the formulae.

Or do you get an actual Error when removing the references and not just a weird high number?

Julian

 

[Shannon E]

Hi Julian,

I'm not sure where I should add that bit of formula.

When I remove the row references (to G:G, H:H, etc.), I get the strange large number you mentioned (1048465).

It's the same with making the row range much larger (G2:G6000, H2:H6000, etc.), except it gives a different large number (5889) for the same formula field.

?? It's strange.

 

[j_unsuitable]

Hey

I just figured out the first part of the formula where it checks for NON blank cells is wrong, I just didnt notice before because there actually always is a date in the ECR Create column.
Because of the wrong formula, when you remove the references the all blank rows now meet the criteria and are counted, thus the weird high number.
Replace all the "<>""" with "<>"&"" and it works for total columns:

=COUNTIFS('ECR Cycle Time - raw'!F:F,"<>"[COLOR=#ff0000][B]&[/B][/COLOR]"",'ECR Cycle Time - raw'!G:G,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")

 

[Shannon E]

That did it!

Thanks so much, Justin! You ROCK!!

 

[Shannon E]

Well, shoot!

It looks like something may be off with the formula. </SPAN></SPAN></SPAN>

When I replaced the raw data with a new data set (re-ran the report with a different date range), the last 3 results wound up being off. From what I can figure out, Excel seems to be ignoring the part about only counting cells in the second criteria range (column I as shown in the image) that contain data.</SPAN>

I did move the criteria set for ignoring items that have a lifecycle of “Cancelled” to first place just to follow a more logical flow but that doesn’t appear to be the problem since it does not resolve when I move it back to the end/criteria range 3 place.</SPAN>

Any ideas??
</SPAN>

 

[j_unsuitable]

Hey again

the "blank" cells in column "I" seem to not really be empty or at least countifs does not see them as such( try =isblank() on them, it will return FALSE).
I've mocked around with your data so much, so i cant be sure if it really works or its just a fluke, but try:

=COUNTIFS('ECR Cycle Time - raw'!I:I,">""",'ECR Cycle Time - raw'!J:J,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")

Alternatively you can select the "blank" cells in "I" and hit delete, then the old formula should return the right value as well.

Julian