Combining CountIF and Vlookup

[dhana_ram]

Hi All!

I'm currently trying to find a way to count how many data "high" in each month. Example: I want to count how many "high" data only in month 1, and how many "Low" in month 4.

I've tried =COUNTIF(VLOOKUP(A2,A2:B18,2,0),"HIGH") but it doesn't work.

Does anyone know how to obtain these data?

Thanks alot!

 

[maabadi]

Welcome to MrExcel Message Board:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	Stock		Month	Count			Month	Stock		Month	Count
2	1	Low		1	1			1/3/2021	Low		1	1
3	1	High		2	3			1/11/2021	High		2	3
4	1	Low		3	1			1/19/2021	Low		3	1
5	1	Low		4	2			1/27/2021	Low		4	1
6	2	High		5	2			2/4/2021	High		5	2
7	2	High		6	1			2/12/2021	High		6	2
8	2	Low						2/20/2021	Low
9	2	High						2/28/2021	High
10	3	Low						3/8/2021	Low
11	3	Low						3/16/2021	Low
12	3	High						3/24/2021	High
13	3	Low						4/1/2021	Low
14	4	Low						4/9/2021	Low
15	4	High						4/17/2021	High
16	4	Low						4/25/2021	Low
17	4	High						5/3/2021	High
18	5	High						5/11/2021	High
19	5	Low						5/19/2021	Low
20	5	Low						5/27/2021	Low
21	5	High						6/4/2021	High
22	6	Low						6/12/2021	Low
23	6	Low						6/20/2021	Low
24	6	High						6/28/2021	High
25	6	Low						7/6/2021	Low
26
27
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=SUMPRODUCT(($B$2:$B$25="High")*($A$2:$A$25=D2))
L2:L7	L2	=SUMPRODUCT(($I$2:$I$25="High")*(--MONTH($H$2:$H$25)=K2))
A3:A25	A3	=IF(MOD(ROW(),4)=2,A2+1,A2)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Unless you have a very old version of Excel, COUNTIFS provides an efficient formula for your counts.
For example

21 10 03.xlsm
	A	B	C	D	E	F
1	Month	Stock		Month	High	Low
2	1	Low		1	1	3
3	1	High		2	3	1
4	1	Low		3	1	3
5	1	Low		4	2	2
6	2	High		5	2	2
7	2	High		6	1	3
8	2	Low
9	2	High
10	3	Low
11	3	Low
12	3	High
13	3	Low
14	4	Low
15	4	High
16	4	Low
17	4	High
18	5	High
19	5	Low
20	5	Low
21	5	High
22	6	Low
23	6	Low
24	6	High
25	6	Low
Count High

Cell Formulas
Range	Formula
E2:F7	E2	=COUNTIFS($A$2:$A$25,$D2,$B$2:$B$25,E$1)

 

[dhana_ram]

Welcome to MrExcel Message Board:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	Stock		Month	Count			Month	Stock		Month	Count
2	1	Low		1	1			1/3/2021	Low		1	1
3	1	High		2	3			1/11/2021	High		2	3
4	1	Low		3	1			1/19/2021	Low		3	1
5	1	Low		4	2			1/27/2021	Low		4	1
6	2	High		5	2			2/4/2021	High		5	2
7	2	High		6	1			2/12/2021	High		6	2
8	2	Low						2/20/2021	Low
9	2	High						2/28/2021	High
10	3	Low						3/8/2021	Low
11	3	Low						3/16/2021	Low
12	3	High						3/24/2021	High
13	3	Low						4/1/2021	Low
14	4	Low						4/9/2021	Low
15	4	High						4/17/2021	High
16	4	Low						4/25/2021	Low
17	4	High						5/3/2021	High
18	5	High						5/11/2021	High
19	5	Low						5/19/2021	Low
20	5	Low						5/27/2021	Low
21	5	High						6/4/2021	High
22	6	Low						6/12/2021	Low
23	6	Low						6/20/2021	Low
24	6	High						6/28/2021	High
25	6	Low						7/6/2021	Low
26
27
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=SUMPRODUCT(($B$2:$B$25="High")*($A$2:$A$25=D2))
L2:L7	L2	=SUMPRODUCT(($I$2:$I$25="High")*(--MONTH($H$2:$H$25)=K2))
A3:A25	A3	=IF(MOD(ROW(),4)=2,A2+1,A2)

Hi, Thanks a lot for your advise. It works!

 

[dhana_ram]

Hi, Thanks a lot for your advise. It works!
And also thanks for reminding to set my excel version info. Will do.

Cheers!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Unless you have a very old version of Excel, COUNTIFS provides an efficient formula for your counts.
For example

21 10 03.xlsm
	A	B	C	D	E	F
1	Month	Stock		Month	High	Low
2	1	Low		1	1	3
3	1	High		2	3	1
4	1	Low		3	1	3
5	1	Low		4	2	2
6	2	High		5	2	2
7	2	High		6	1	3
8	2	Low
9	2	High
10	3	Low
11	3	Low
12	3	High
13	3	Low
14	4	Low
15	4	High
16	4	Low
17	4	High
18	5	High
19	5	Low
20	5	Low
21	5	High
22	6	Low
23	6	Low
24	6	High
25	6	Low
Count High

Cell Formulas
Range	Formula
E2:F7	E2	=COUNTIFS($A$2:$A$25,$D2,$B$2:$B$25,E$1)

 

[maabadi]

You're Welcome & Thanks for follow-up.

 

[Peter_SSs]

Hi, Thanks a lot for your advise. It works!
And also thanks for reminding to set my excel version info. Will do.

You're welcome. And thanks for updating your profile.