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]

I removed the duplicates in ECO Cycle Time - raw first via the Data ribbon function (much easier than building a formula to do so given the extra conditions) and cleared all the fake blank cells (apparently downloaded from a database) with alt-f11 ctrl-G selection.value=selection.value, reformatted them as date/time, then tried these formulas which match what you said they should be:

Excel 2010
	M	N	O	P	Q
2	Total	0-30 Days	31-60 Days	61-90 Days	Over 120 Days
3	0	0	0	0	0
4	4	0	4	0	0
5	26	1	22	2	1
6	53	50	1	0	2
7	34	33	0	0	1
8	117
WIP Aging

Cell Formulas
Range	Formula
N4	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115<=30),--('ECR Cycle Time - raw'!$H$2:$H$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
N5	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115<=30),--('ECR Cycle Time - raw'!$J$2:$J$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
N6	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839<=30),--('ECO Cycle Time - raw'!$J$2:$J$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
N7	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839<=30),--('ECO Cycle Time - raw'!$I$2:$I$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
O4	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115>=31),--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115<=60),--('ECR Cycle Time - raw'!$H$2:$H$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
O5	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115>=31),--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115<=60),--('ECR Cycle Time - raw'!$J$2:$J$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
O6	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839>=31),--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839<=60),--('ECO Cycle Time - raw'!$J$2:$J$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
O7	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839>=31),--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839<=60),--('ECO Cycle Time - raw'!$I$2:$I$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
P4	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115>=61),--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115<=90),--('ECR Cycle Time - raw'!$H$2:$H$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
P5	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115>=61),--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115<=90),--('ECR Cycle Time - raw'!$J$2:$J$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
P6	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839>=61),--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839<=90),--('ECO Cycle Time - raw'!$J$2:$J$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
P7	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839>=61),--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839<=90),--('ECO Cycle Time - raw'!$I$2:$I$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
Q4	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115>=120),--(TODAY()-1-'ECR Cycle Time - raw'!$G$2:$G$115<=3650),--('ECR Cycle Time - raw'!$H$2:$H$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
Q5	=SUMPRODUCT(--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115>=120),--(TODAY()-1-'ECR Cycle Time - raw'!$I$2:$I$115<=3650),--('ECR Cycle Time - raw'!$J$2:$J$115=""),--('ECR Cycle Time - raw'!$C$2:$C$115<>"Cancelled"))
Q6	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839>=120),--(TODAY()-1-'ECO Cycle Time - raw'!$I$2:$I$839<=3650),--('ECO Cycle Time - raw'!$J$2:$J$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
Q7	=SUMPRODUCT(--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839>=120),--(TODAY()-1-'ECO Cycle Time - raw'!$H$2:$H$839<=3650),--('ECO Cycle Time - raw'!$I$2:$I$839=""),--('ECO Cycle Time - raw'!$C$2:$C$839<>"Cancelled"))
M5	=SUM(N5:Q5)
M6	=SUM(N6:Q6)
M7	=SUM(N7:Q7)
M8	=SUM(M3:M7)

the countifs may be off because we're at today()+1

 

[Shannon E]

Thanks so much for the response!

This week has been nuts so I haven't had time to do too much digging into your response yet. I'm hoping I will have time today.

Thank you for the data ribbon Remove Duplicates tip. I'm not familiar with that particular function so didn't even think of it! I did give that a quick try and it seems to work as needed.

 

[Shannon E]

It looks like the formulas work for the ECR Submit row (row 4 in your snapshot) but I get a #VALUE! error in the other fields rather than number results.

I'm not quite sure what you meant by clearing all the fake blank cells. The data on the ECR and ECO Cycle Time - raw tabs are from a report out of a system/database, as you mentioned. All of the blank cells I spot checked were blank; there were no spaces entered or anything like that.

It looks like I failed to mention previously that I am trying to get formulas that will work for any number of rows on the raw (data) tabs without editing them. The plan is to run the reports for the desired timeframe, paste the data into those raw tabs, and then have the table with these formulas for the Total numbers of the different states (ECR Create, ECR Submit, etc.) and the aging (0-30 Days, 31-60 Days, etc.) display the counts automatically so that they can then be charted.

It is easy enough to put instructions for the person responsible for pasting the data in and copying the charts for metric reporting to clear out the duplicates as you noted. However, I would like to avoid necessitating updating of the formulas as that is a bit advanced.

Any ideas?

 

[sheetspread]

All of the blank cells I spot checked were blank; there were no spaces entered or anything like that.

test the cell with =isblank(a1). It can be false without spaces.

 

[Shannon E]

Thanks for the quick response!
I did that and see now what you are referring to but am still not sure how to clear them.

cleared all the fake blank cells (apparently downloaded from a database) with alt-f11 ctrl-G selection.value=selection.value, reformatted them as date/time

I tried this but it doesn't seem to work...

 

[sheetspread]

I deleted my copy of the file you posted earlier and the link is now dead. Can you restore it or send me another?

 

[Shannon E]

Sorry, I was tardy in renewing my web hosting. It's back up and running now.

 

[sheetspread]

Database imports are often formatted as text so for these calculations you have to get them back to number/date/time (which alt-f11 ctrl-G selection.value=selection.value does as well as non VBA methods). You're also checking if adjacent cells are blank and while ="" will recognize true and false blanks alike, changing the format might also change "blanks" to zeroes and hinder the formula. So check them again after using alt-f11 ctrl-G selection.value=selection.value. Anyway your countifs formulas work now after some minor changes:

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L
1						s/b	(1 Dec 2015)
2	Total	0-30 Days	31-60 Days	61-90 Days	Over 120 Days	Total	0-30 Days	31-60 Days	61-90 Days	Over 120 Days
3	0	0	0	0	0	0	0	0	0	0
4	4	0	4	0	0	4	0	4	0	0
5	26	1	22	2	1	26	1	22	2	1
6	53	50	1	0	2	53	50	1	0	2
7	34	33	0	0	1	34	33	0	0	1
8	117					117
WIP Aging

Cell Formulas
Range	Formula
B3	=COUNTIFS('ECR Cycle Time - raw'!C:C,"<>Cancelled",'ECR Cycle Time - raw'!F:F,"<>"&"",'ECR Cycle Time - raw'!G:G,"")
B4	=COUNTIFS('ECR Cycle Time - raw'!C:C,"<>Cancelled",'ECR Cycle Time - raw'!G:G,"<>"&"",'ECR Cycle Time - raw'!H:H,"")
B5	=COUNTIFS('ECR Cycle Time - raw'!I:I,">0",'ECR Cycle Time - raw'!J:J,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
B6	=COUNTIFS('ECO Cycle Time - raw'!I:I,">0",'ECO Cycle Time - raw'!J:J,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
B7	=COUNTIFS('ECO Cycle Time - raw'!C:C,"<>Cancelled",'ECO Cycle Time - raw'!H:H,">0",'ECO Cycle Time - raw'!I:I,"")
B8	=SUM(B3:B7)
C3	=COUNTIFS('ECR Cycle Time - raw'!F:F,">="&TODAY()-30,'ECR Cycle Time - raw'!G:G,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
C4	=COUNTIFS('ECR Cycle Time - raw'!G:G,">="&TODAY()-30-7,'ECR Cycle Time - raw'!H:H,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
C5	=COUNTIFS('ECR Cycle Time - raw'!I:I,">="&TODAY()-30-7,'ECR Cycle Time - raw'!J:J,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
C6	=COUNTIFS('ECO Cycle Time - raw'!I:I,">="&TODAY()-30-7,'ECO Cycle Time - raw'!J:J,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
C7	=COUNTIFS('ECO Cycle Time - raw'!H:H,">="&TODAY()-30-7,'ECO Cycle Time - raw'!I:I,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
D3	=COUNTIFS('ECR Cycle Time - raw'!F:F,"<="&TODAY()-30,'ECR Cycle Time - raw'!F:F,">="&TODAY()-60,'ECR Cycle Time - raw'!G:G,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
D4	=COUNTIFS('ECR Cycle Time - raw'!G:G,">"&TODAY()-61-7,'ECR Cycle Time - raw'!G:G,"<"&TODAY()-30-7,'ECR Cycle Time - raw'!H:H,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
D5	=COUNTIFS('ECR Cycle Time - raw'!I:I,">"&(TODAY()-61-7),'ECR Cycle Time - raw'!I:I,"<"&(TODAY()-30-7),'ECR Cycle Time - raw'!J:J,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
D6	=COUNTIFS('ECO Cycle Time - raw'!I:I,"<="&TODAY()-30-7,'ECO Cycle Time - raw'!I:I,">="&TODAY()-60-7,'ECO Cycle Time - raw'!J:J,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
D7	=COUNTIFS('ECO Cycle Time - raw'!H:H,"<="&TODAY()-30-7,'ECO Cycle Time - raw'!H:H,">="&TODAY()-60-7,'ECO Cycle Time - raw'!I:I,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
E3	=COUNTIFS('ECR Cycle Time - raw'!F:F,"<="&TODAY()-60,'ECR Cycle Time - raw'!F:F,">="&TODAY()-90,'ECR Cycle Time - raw'!G:G,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
E4	=COUNTIFS('ECR Cycle Time - raw'!G:G,">"&TODAY()-91-7,'ECR Cycle Time - raw'!G:G,"<"&TODAY()-60-7,'ECR Cycle Time - raw'!H:H,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
E5	=COUNTIFS('ECR Cycle Time - raw'!I:I,">"&(TODAY()-91-7),'ECR Cycle Time - raw'!I:I,"<"&(TODAY()-60-7),'ECR Cycle Time - raw'!J:J,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
E6	=COUNTIFS('ECO Cycle Time - raw'!I:I,"<="&TODAY()-60-7,'ECO Cycle Time - raw'!I:I,">="&TODAY()-90-7,'ECO Cycle Time - raw'!J:J,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
E7	=COUNTIFS('ECO Cycle Time - raw'!H:H,"<="&TODAY()-60-7,'ECO Cycle Time - raw'!H:H,">="&TODAY()-90-7,'ECO Cycle Time - raw'!I:I,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
F3	=COUNTIFS('ECR Cycle Time - raw'!F:F,"<="&TODAY()-120,'ECR Cycle Time - raw'!G:G,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
F4	=COUNTIFS('ECR Cycle Time - raw'!G:G,"<="&TODAY()-120-7,'ECR Cycle Time - raw'!H:H,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
F5	=COUNTIFS('ECR Cycle Time - raw'!I:I,"<="&TODAY()-120-7,'ECR Cycle Time - raw'!J:J,"",'ECR Cycle Time - raw'!C:C,"<>Cancelled")
F6	=COUNTIFS('ECO Cycle Time - raw'!I:I,"<="&TODAY()-120-7,'ECO Cycle Time - raw'!J:J,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
F7	=COUNTIFS('ECO Cycle Time - raw'!H:H,"<="&TODAY()-120,'ECO Cycle Time - raw'!I:I,"",'ECO Cycle Time - raw'!C:C,"<>Cancelled")
G8	=SUM(G3:G7)

 

[Shannon E]

I tried the alt-f11, ctrl-G, selection.value=selection.value and it doesn't seem to work. I'm not sure what I am doing wrong. Is there some in-between step I'm missing? (I have very little experience with VBA.)

All of the aging date ranges are displaying zero. I did another check of the numbers since it's been a week and came up with the following (I believe I should be getting these numbers):