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

 

[Shannon E]

That's what is weird. I did go into those cells to ensure they were actually blank and they were. ??

It's baffling!

I tried the other formula you provided and it seems to work. Thank you again, Julian, for the quick response!

Now I get to try to figure out how to get the formulas to work for the aging (0-30 days, etc.). Wish me luck!

 

[j_unsuitable]

I googled a bit and it seems to have to do with formulas being pasted as values. If you have a formula return "" and then paste it as value, the cell appears as blank but in reality its not empty anymore. And i guess thats what happens when you export the data from whatever program youre using.
Another workaround i found while googling:

First, save your spreadsheet or create a test copy so that if you make a mistake, you don't lose your original spreadsheet. Then click on Find/Replace, and leave the 'Find What' box blank, then in the 'Replace With' box, put a silly word you know the spreadsheet does not contain, then click on 'Find Next' to make sure it finds a blank cell, then click on 'Replace All'. Excel will go through and replace all the 'blank' cells with the silly word you chose. Then get rid of the silly word by doing another Find/Replace. In 'Find What', put the silly word, and then make sure the 'Replace With' box is completely empty (no spaces or characters).

You now probably know my work is trial and error most of the time..so if you have any new questions you might want to start a new thread so people who actually know what they are doing will respond, but also feel free to come back here, i actually learned quite a bit by trying to help you haha
(Just dont call me Justin again )

 

[Shannon E]

lol
Thanks, Julian!
I think I had just worked with a Justin at the time so had Justin on the brain.