Count and Ignore Zero or Blanks

[kumara_faith]

Hi,

I have the following table:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Staff Name	01-01-18	02-01-18	03-01-18	04-01-18	05-01-18	06-01-18	07-01-18	08-01-18	09-01-18	10-01-18	11-01-18	12-01-18	13-01-18	14-01-18
5	John	3	3	3	3	3			3	3	3	3	3
6	Amanda	3	3	3	3	3			3	3	3	3	3
7	Julie		3			3			3		3	3
8	Ally	3	3	3	3	3			3	3	3	3	3
9	Total	9	12	9	9	12		0	12	9	12	12	9	0	0
Sheet1

I need to calculate the number of times the rows highlighted in yellow is less than 12 but to ignore cells which are blank or zero. I tried using the the formula below but it does not seems to work. Appreciate any assistance. The correct answer should be 5.

=COUNTIFS(C9:P9," <12")-COUNTBLANK(C9:P9)-COUNTIF(C9:P9,0)

 

[Michael M]

Try using

=COUNTIFS(C9:P9,">0", C9:P9,"<12")

 

[kumara_faith]

Hi Michael,

Thank you. That worked. Appreciate your time and patience. You have a good day ahead.

 

[kumara_faith]

Hi ,

The range is in sheet 1 and the criteria(s) are in sheet 2. I just realized countifs does not work. Is there another way ? I tried the following formula but does not seems to work. Appreciate all help.

=SUMPRODUCT(('YTD LEAVE VIEW'!G20:AK20)>0*('YTD LEAVE VIEW'!G20:AK20)<'SUMMARY 2'!C7)

 

[Michael M]

which one is sheet 1 ?
Where is the formula going to reside, sheet1 or 2 ??