Count Days With Certain Criteria...

[Russ At Index]

Hello Everyone ,

Stumped with this I'm afraid , hoping someone in the Excel World can help me out .

I have the following headers :
MonAm MonPm TueAm TuePm WedAm WedPm ThuAm ThuPm FriAm FriPm SatAm SatPm SunAm SunPm
12 0 0 0 5 5 0 0 6 0 0 0 0 1

I would like a formula that tells me that the total number of full days there was a figure > 0 entered , the example above
would return a answer of : 4

Thank you ,

Russ

 

[mse330]

Assuming your data is placed the range A1:N2 ... Try the below formula in cell O2

=COUNTIF(A2:N2,">0")

By the way, shouldn't the answer be 5 & not 4 ?

 

[mse330]

Just reading your question again, what do you mean by "number of full days" ? Both Am & Pm of the same day ? In the example you provided, only WedAm & WedPm have values above 0 only so how did you calculate the 4 ?

 

[Russ At Index]

Hi mse330 ,
Thank you for your reply , and you are correct , my mistake the result i am looking for in the example provided is indeed 5.
However , your formula returns an answer of 6 .
To try to make it clearer , Mon has two cells ,am & pm , if both or just one cell has a number >0 count it as 1 , Tue for instance
has two cells am & pm with a figure of zero in each , that does not need to be counted as there was no activity that day.
It would be no problem if there were only one cell er day , but my Boss wants both !!!!

Thanks , enjoy your day ,

Russ.

 

[mse330]

Hey Russ,

If you want to count any occurance that is above 0 then my first formula supplied will do what we need. However, if you want to count the days that have either AM, PM or both as 1 then the below formula should do that although I am sure other forum members might come up with neater shorter formula

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	MonAm	MonPm	TueAm	TuePm	WedAm	WedPm	ThuAm	ThuPm	FriAm	FriPm	SatAm	SatPm	SunAm	SunPm	Answer
2	12	0	0	0	5	5	0	0	6	0	0	0	0	1	5
3	12	0	0	0	5	5	0	0	6	0	0	0	0	1	4
Sheet1

Cell Formulas
Range	Formula
O2	=COUNTIF(A2:N2,">0")
O3	=IF(SUM(A3:B3)>0,1,0)+IF(SUM(C3:D3)>0,1,0)+IF(SUM(E3:F3)>0,1,0)+IF(SUM(G3:H3)>0,1,0)+IF(SUM(I3:J3)>0,1,0)+IF(SUM(K3:L3)>0,1,0)+IF(SUM(M3:N3)>0,1,0)

 

[Russ At Index]

Hi mse330 ,

Your second option works perfectly , thank you very much indeed , appreciated.

Regards

Russ

 

[mse330]

Glad to help & thanks for the feedback