COUNTIFS With Multiple Conditions

[Shannon E]

Hello,</SPAN>

I have a two part question I’m hoping someone can help with. My brain is so fried at this point, I can't think straight about this anymore! </SPAN>

1. How do I update the formulas in the Total column (B) to only count one instance of a “Name” on the data (“raw”) tab (also column B)?</SPAN>

The numbers in the right-hand section (s/b) show what the results should be and you can see that they don't quite match up.

I had great help in getting the formulas to count the number of items meeting my required criteria but I missed one thing – apparently some numbers are duplicated and I only want to count one instance of a particular “ECO Name” (number).</SPAN>​

The data on the ECO Cycle Time - raw tab is copied out of a system-generated report and for some reason it lists multiple rows for some ECO numbers. I only want to count one instance of the number and don’t want to have to manually find the duplicate rows and delete them.
</SPAN>
From what I can tell, the data is the same in all the duplicated rows.
</SPAN>​

1. How do I get the aging formulas (0-30 Days, 31-60 Days, etc.) to return the appropriate number of ECOs (ECO Name)?

</SPAN>

I need to count the number of ECOs (unique rows/instances of ECO Names) that have dates falling into the given timeframes (0-30 days in the past, 31-60 days in the past, etc.) and meet the given criteria from the Total column. </SPAN>

i.e. For ECO Design Work (AFI) I need a count of the (unique) ECOs (ECO Name) on the ECO Cycle Time – raw tab that do NOT have ECO State (column C) of “Cancelled”, have NO date/data in ECO Review (column I), AND have a date that fits the given timeframe (0-30 days in the past, 31-60 days in the past, etc.).</SPAN>​

Here is a link to a working file: http://www.shannonmherickson.com/Help/Sample_Metrics.xlsx

Does this make sense?</SPAN>

 

[sheetspread]

Did you highlight all the dates first?

 

[Shannon E]

That would be what I was missing!

It looks like it is working properly now. Now hopefully it works as expected when I try it with new report data.

Thanks so much!

Did you highlight all the dates first?