Count one cell based off value of another cell

[ColdplayCoaster]

Greetings again,

What I am trying to accomplish is count one of two cells (H5 or K5) as 1 if either of those cells are less than a certain date (5/13) only if C5 has a value in it.

I know that's a lot going on in 1 particular thought, but I was pretty confident I could do it via COUNTIF but I have run into some hiccups. Anyone have a great idea out there?

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M
3						1		0	10		2
4	CONFIRM	BACK. CHECK		SCHOOL	APPROVED TIME OFF	WKNDS BEFORE			FULL TIME DATES
5	X				Tue. @ 11:30; Thu. until 6/17; 9/1	5/9	-	6/15	6/17	-	9/5
6	X			Ferris State University	5/27-5/29; 7/7-7/8		-		5/9	-	8/24
7	X	X					-		5/9	-	10/30
8	X	X		Kent State University	5/10; 5/12; 5/17; 5/19		-		5/10	-	8/24
9	X			Michigan State University			-		5/10	-	8/27
10	X	X		Ferris State University			-		5/10	-	8/24
11	X	X		Lansing Community College			-		5/17	-	8/19
12	X			Ohio State University			-		6/13	-	9/11
13	X	X		Ohio University	7/5		-		6/14	-	9/2
14					7/2-7/5		-		5/9	-	7/22
15				Kent State University			-		5/10	-	8/24
16	X				6/25-7/4		-		5/9	-	10/30
17	X			University of Akron			-		5/9	-	8/18
18	X			Heidelberg University			-		5/16	-	8/25
19				St. Clair Community College	7/1-7/3		-		5/10	-	8/18
4066

Excel 2007

Cell Formulas
Range	Formula
H3	=COUNTIF(H5:H39,"<=5/13")
J3	=COUNTIF(J5:J39,"<=5/14")
K3	=COUNTIF(K5:K39,"<=5/13")
M3	=COUNTIF(M5:M39,">=10/30")

 

[T. Valko]

Greetings again,

What I am trying to accomplish is count one of two cells (H5 or K5) as 1 if either of those cells are less than a certain date (5/13) only if C5 has a value in it.

I know that's a lot going on in 1 particular thought, but I was pretty confident I could do it via COUNTIF but I have run into some hiccups. Anyone have a great idea out there?

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M
3						1		0	10		2
4	CONFIRM	BACK. CHECK		SCHOOL	APPROVED TIME OFF	WKNDS BEFORE			FULL TIME DATES
5	X				Tue. @ 11:30; Thu. until 6/17; 9/1	5/9	-	6/15	6/17	-	9/5
6	X			Ferris State University	5/27-5/29; 7/7-7/8		-		5/9	-	8/24
7	X	X					-		5/9	-	10/30
8	X	X		Kent State University	5/10; 5/12; 5/17; 5/19		-		5/10	-	8/24
9	X			Michigan State University			-		5/10	-	8/27
10	X	X		Ferris State University			-		5/10	-	8/24
11	X	X		Lansing Community College			-		5/17	-	8/19
12	X			Ohio State University			-		6/13	-	9/11
13	X	X		Ohio University	7/5		-		6/14	-	9/2
14					7/2-7/5		-		5/9	-	7/22
15				Kent State University			-		5/10	-	8/24
16	X				6/25-7/4		-		5/9	-	10/30
17	X			University of Akron			-		5/9	-	8/18
18	X			Heidelberg University			-		5/16	-	8/25
19				St. Clair Community College	7/1-7/3		-		5/10	-	8/18
4066

Excel 2007

Cell Formulas
Range	Formula
H3	=COUNTIF(H5:H39,"<=5/13")
J3	=COUNTIF(J5:J39,"<=5/14")
K3	=COUNTIF(K5:K39,"<=5/13")
M3	=COUNTIF(M5:M39,">=10/30")

Use a cell to hold the date criteria:

• A1 = 5/13/2011

Then:

=SUMPRODUCT(--(C5:C39<>""),--((H5:H39< A1)+(K5:K39< A1) > 0))<?XML:NAMESPACE PREFIX = A1)+(K5 /><A1)+(K5:K39<A1)>

I'm assuming there will be no empty cells in the date ranges H5:H39 and K5:K39.

If there might be then we'll need to tweak the formula a bit to account for that.</A1)+(K5:K39<A1)>

 

[ColdplayCoaster]

Thanks for your quick response. There may be times when I would need to count empty cells without having to update each formula. There will be 13 sheets within the worksheet so that would be tedious.

Also, my calculations should have resulted in a count of 8:

Rows 5, 6, 7, 8, 9, 10, 16 & 17 are the only rows where Confirmed has a value AND either date is before 5/13. Any thoughts?

 

[T. Valko]

Thanks for your quick response. There may be times when I would need to count empty cells without having to update each formula. There will be 13 sheets within the worksheet so that would be tedious.

Also, my calculations should have resulted in a count of 8:

Rows 5, 6, 7, 8, 9, 10, 16 & 17 are the only rows where Confirmed has a value AND either date is before 5/13. Any thoughts?

Ok, then we have to account for empty cells.

Try it like this...

<?XML:NAMESPACE PREFIX = A1))+((K5 /><A1))+((K5:K19<>=SUMPRODUCT(--(C5:C19<>""),--(((H5:H19<>"")*(H5:H19 < A1))+((K5:K19<>"")*(K5:K19 < A1)) > 0))<A1))>
</A1))+((K5:K19<>

 

[ColdplayCoaster]

That does the trick! Thank you!

Now, if I wanted to do the same thing but only count "Confirms" (Column C) if the end date (Column M and P), I'm assuming this would be the formula:

=SUMPRODUCT(--(C5:C19<>""),--(((M5:M19<>"")*(M5:M19 = D2))+((P5:P19<>"")*(P5:P19 = D2))>0))

Actually, that did it! Again, thank you so much! I'm truly amazed at your speed and expertise.

 

[T. Valko]

That does the trick! Thank you!

Now, if I wanted to do the same thing but only count "Confirms" (Column C) if the end date (Column M and P), I'm assuming this would be the formula:

=SUMPRODUCT(--(C5:C19<>""),--(((M5:M19<>"")*(M5:M19 = D2))+((P5:P19<>"")*(P5:P19 = D2))>0))

Actually, that did it! Again, thank you so much! I'm truly amazed at your speed and expertise.

If you're testing for straight equality...

Cells in the range equal this value

Then you don't need to also account for empty cells.

Maybe you meant this...

=SUMPRODUCT(--(C5:C19<>""),--((M5:M19=D2)+(P5:P19=D2)>0))