Countif with based on multiple criteria

[Lukma]

Hi friends

Please i was wondering if anyone could help out with a formula that could count based on multiple criteria

i need to count a range of column with different value the meet and do not meet that if ( Half equal 10 and Full equal 16) so i need to count are many that meet and do not meet for each criteria

here is my table appreciate if anyone can help me with a formula that can count based on multiple criteria

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Full	Half
3		16	10
4													Meet	Do Not meet
5		A-221	Half	9	8	12	19	25	10	10	10	15
6		A-221	Full	10	25	15	19	16	14	16	45	12
7		A-222	Half	9	8	12	19	25	10	10	10	15
8		A-223	Full	10	25	15	19	16	14	16	45	12
9		A-224	Half	9	8	12	19	25	10	10	10	15
10		A-225	Half	9	8	12	19	25	10	10	10	15
11
Sheet1

 

[Joe4]

Place this formula in cell M5:

=IF($C5=$B$2,COUNTIF($D5:$L5,">=" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,">=" & $C$3),""))

and this formula un cell N5:

=IF($C5=$B$2,COUNTIF($D5:$L5,"<" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,"<" & $C$3),""))

and copy down for all rows.

 

[lrobbo314]

Another way.

bRoster.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Full	Half
3		16	10
4													Meet	Do Not meet
5		A-221	Half	9	8	12	19	25	10	10	10	15	3	6
6		A-221	Full	10	25	15	19	16	14	16	45	12	2	7
7		A-222	Half	9	8	12	19	25	10	10	10	15	3	6
8		A-223	Full	10	25	15	19	16	14	16	45	12	2	7
9		A-224	Half	9	8	12	19	25	10	10	10	15	3	6
10		A-225	Half	9	8	12	19	25	10	10	10	15	3	6
Sheet2

Cell Formulas
Range	Formula
M5:M10	M5	=SUMPRODUCT(--(D5:L5=LOOKUP(C5,$B$2:$C$2,$B$3:$C$3)))
N5:N10	N5	=COUNTA(D5:L5)-M5

 

[Joe4]

Follow-up:

Since you did not post any expected results, your expected output is a little unclear, and the two replies made different assumptions.
I assumed that you wanted to do the following:
- for Half: count anything 10 and above
- for Full: count anything 16 and above

lrobbo314 assumes that you were looking for those values to count if they were EXACTLY 10 and 16, respectively.

Like I said, I am not sure which one you were looking for, but if lrobbo314's assumption is correct, you could also get it using my method, with a few minor changes:
M5:

=IF($C5=$B$2,COUNTIF($D5:$L5,$B$3),IF($C5=$C$2,COUNTIF($D5:$L5,$C$3),""))

N5:

=IF($C5=$B$2,COUNTIF($D5:$L5,"<>" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,"<>" & $C$3),""))

 

[Lukma]

Place this formula in cell M5:

=IF($C5=$B$2,COUNTIF($D5:$L5,">=" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,">=" & $C$3),""))

and this formula un cell N5:

=IF($C5=$B$2,COUNTIF($D5:$L5,"<" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,"<" & $C$3),""))

and copy down for all rows.

Hi Joe4
Thanks so much with the formula but still i need the formula to count column with blue as meet which will be 5 that meet the criteria and the red will be do not meet which will count will be 4

appreciate

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Full	Half
3		16	10
4													Meet	Do Not meet
5		A-221	Half	9	8	12	19	25	10	10	10	15	7	2
6		A-221	Full	10	25	15	19	16	14	16	45	12	5	4
7		A-222	Half	9	8	12	19	25	10	10	10	15	7	2
8		A-223	Full	10	25	15	19	16	14	16	45	12	5	4
9		A-224	Half	9	8	12	19	25	10	10	10	15	7	2
10		A-225	Half	9	8	12	19	25	10	10	10	15	7	2
11
Sheet1

Cell Formulas
Range	Formula
M5:M10	M5	=IF($C5=$B$2,COUNTIF($D5:$L5,">=" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,">=" & $C$3),""))
N5:N10	N5	=IF($C5=$B$2,COUNTIF($D5:$L5,"<" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,"<" & $C$3),""))

 

[Lukma]

Another way.

Hi.​

Thanks so much both Joe4 & Irobbo314 with the formula, Column M5 to consider as 5 that meet and column N to be consider as 4 that do not meet

Meet Do note Meet
M=count 5 N = 4

Appreciate with any formula that can give this count based on the criteria range

 

[Joe4]

Thanks so much with the formula but still i need the formula to count column with blue as meet which will be 5 that meet the criteria and the red will be do not meet which will count will be 4

Wow, OK, so it appears that you actually want to count anything that is that number (10) and lower. Is that correct (I am not sure how we were supposed to figure that out from the original post!).

If that is the case, then use:
M5:

=IF($C5=$B$2,COUNTIF($D5:$L5,"<=" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,"<=" & $C$3),""))

N5:

=IF($C5=$B$2,COUNTIF($D5:$L5,">" & $B$3),IF($C5=$C$2,COUNTIF($D5:$L5,">" & $C$3),""))

 

[Lukma]

Hi

thanks great so releave thanks a million Joe4

 

[Joe4]

You are welcome.

I hope you understand how the formula works. If not, feel free to ask any questions.

 

[Lukma]

hI Joe4

Please i need to Ask supposing the value are in time format will it still count and ignore blanks cells

if it is possible i would prefer the time format count

Appreciate your feedback with a formula that could count if the value in cell are time format