Excel formula to count if cell value matches with specified range

[Lekha mohanty]

In A column I have data from 1 to 100, in B column I have "Yes" or "No".

In C1 I want a formula total of "yes" from Serial (1 to 30) + (41 to 60) + (91 to 100), Similarly in D1 total of "yes" from Serial (31 to 40) + (61 to 90)

what should be the formula ??

exp garuda.xlsx
	A	B	C	D
1	1	Yes	31	17
2	2	Yes
3	3	Yes
4	4	No
5	5	No
6	6	Yes
7	7	No
8	8	Yes
9	9	Yes
10	10	No
11	11	No
12	12	No
13	13	No
14	14	Yes
15	15	Yes
16	16	Yes
17	17	No
18	18	No
19	19	Yes
20	20	No
21	21	Yes
22	22	Yes
23	23	No
24	24	No
25	25	No
26	26	No
27	27	Yes
28	28	Yes
29	29	Yes
30	30	No
31	31	No
32	32	Yes
33	33	No
34	34	Yes
35	35	Yes
36	36	No
37	37	No
38	38	No
39	39	No
40	40	Yes
41	41	Yes
42	42	Yes
43	43	No
44	44	No
45	45	Yes
46	46	No
47	47	Yes
48	48	Yes
49	49	No
50	50	No
51	51	No
52	52	No
53	53	Yes
54	54	Yes
55	55	Yes
56	56	No
57	57	No
58	58	Yes
59	59	No
60	60	Yes
61	61	Yes
62	62	No
63	63	No
64	64	No
65	65	No
66	66	Yes
67	67	Yes
68	68	Yes
69	69	No
70	70	No
71	71	Yes
72	72	No
73	73	Yes
74	74	Yes
75	75	No
76	76	No
77	77	No
78	78	No
79	79	Yes
80	80	Yes
81	81	Yes
82	82	No
83	83	No
84	84	Yes
85	85	No
86	86	Yes
87	87	Yes
88	88	No
89	89	No
90	90	No
91	91	No
92	92	Yes
93	93	Yes
94	94	Yes
95	95	No
96	96	No
97	97	Yes
98	98	No
99	99	Yes
100	100	Yes
Sheet1

 

[hrayani]

in C1

=COUNTIF(B1:B30,"Yes")+COUNTIF(B41:B60,"Yes")+COUNTIF(B91:B100,"Yes")

in D1

=COUNTIF(B31:B40,"Yes")+COUNTIF(B61:B90,"Yes")

 

[Lekha mohanty]

Thank you so much

 

[hrayani]

Thank you so much

you are welcome

 

[Peter_SSs]

Suggestion ..
Keep the same formula for D1, but use this much simpler one for C1:

=COUNTIF(B1:B100,"Yes")-D1