COUNTIFS WITH NUMERIC CONSTRAINT

dewotorf

New Member
Joined
Feb 25, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I am looking for a way to count a specific number in a column provided the corresponding cell in another column is a number i.e.(not text). Consider the table below as an example;

I want to count only 70% marks in column B provided the corresponding value in column C is a number. In this case the answer will be 2.


1614302068924.png
 

Attachments

  • countifs.PNG
    countifs.PNG
    5.6 KB · Views: 2

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
=SUMPRODUCT(--(IF(ISNUMBER(C2:C8),B2:B8,0)>=E2)
Many thanks. However, it didn't work with the data i am working with.

countifsnumber.xlsx
ABC
1CandidateTest 1Test 2
2AB17n/a
3AB23n/a
4AB33n/a
5AB43n/a
6AB537
7AB637
8AB737
9AB8ab7
10AB9ab3
11AB1023
12AB1117
13AB1237
14AB131DO
15AB14ab3
16AB15ab7
17AB1613
18AB17ab7
19AB18ab7
20AB1937
21AB20ab3
22AB21abDO
23AB22ab1
24AB23ab3
25AB24ab1
26AB25ab2
27AB2627
28AB2712
29AB2813
30AB291DO
31AB3033
32AB3113
33AB3233
34AB33ab1
35AB3417
36AB3533
37AB3611
38AB377
39AB381
40AB39DODO
41AB4017
42AB4137
43AB4237
44AB4337
45AB4433
46AB45ab7
47AB463n/a
48AB4737
49AB48ab7
50AB4973
51AB503n/a
52AB511n/a
53AB527n/a
54AB533n/a
55AB543n/a
56AB557n/a
57AB563n/a
58AB573n/a
Sheet1


I want to count how many times number 3 appears in column B on condition that the corresponding cell in column C is a number. In this case, the answer is 13 counts.
you could use the following formula in E3

=SUMPRODUCT(--(IF(ISNUMBER(C2:C8),B2:B8,0)>=E2))


View attachment 33116

Kind regards

Saba
 
Upvote 0
Hi,

Many thanks. However, it didn't work with the data i am working with.

I want to count how many times number 3 appears in column B on condition that the corresponding cell in column C is a number. In this case, the answer is 13 counts.

That's because the sample you shown in OP is different than the data you're working with in Post #3.
Hopefully, your new sample is an accurate representation of your actual data.
Try this:

Book3.xlsx
ABCDEF
1CandidateTest 1Test 2Criteria3
2AB17n/aCount13
3AB23n/a
4AB33n/a
5AB43n/a
6AB537
7AB637
8AB737
9AB8ab7
10AB9ab3
11AB1023
12AB1117
13AB1237
14AB131DO
15AB14ab3
16AB15ab7
17AB1613
18AB17ab7
19AB18ab7
20AB1937
21AB20ab3
22AB21abDO
23AB22ab1
24AB23ab3
25AB24ab1
26AB25ab2
27AB2627
28AB2712
29AB2813
30AB291DO
31AB3033
32AB3113
33AB3233
34AB33ab1
35AB3417
36AB3533
37AB3611
38AB377
39AB381
40AB39DODO
41AB4017
42AB4137
43AB4237
44AB4337
45AB4433
46AB45ab7
47AB463n/a
48AB4737
49AB48ab7
50AB4973
51AB503n/a
52AB511n/a
53AB527n/a
54AB533n/a
55AB543n/a
56AB557n/a
57AB563n/a
58AB573n/a
59
60
Sheet798
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT((B2:B60=F1)*(ISNUMBER(C2:C60)))
 
Upvote 0
Thank you jtakw

Another solution may be to use the following array formula in E2. It needs to be entered by pressing Shift + Control + Enter together for the data shown below.

=SUM(--((IF(ISNUMBER(C2:C10),IF(ISNUMBER(B2:B10),B2:B10,0),0)>=F2)))

1614306583337.png


Kind regards

Saba
 
Upvote 0
Hi,



That's because the sample you shown in OP is different than the data you're working with in Post #3.
Hopefully, your new sample is an accurate representation of your actual data.
Try this:

Book3.xlsx
ABCDEF
1CandidateTest 1Test 2Criteria3
2AB17n/aCount13
3AB23n/a
4AB33n/a
5AB43n/a
6AB537
7AB637
8AB737
9AB8ab7
10AB9ab3
11AB1023
12AB1117
13AB1237
14AB131DO
15AB14ab3
16AB15ab7
17AB1613
18AB17ab7
19AB18ab7
20AB1937
21AB20ab3
22AB21abDO
23AB22ab1
24AB23ab3
25AB24ab1
26AB25ab2
27AB2627
28AB2712
29AB2813
30AB291DO
31AB3033
32AB3113
33AB3233
34AB33ab1
35AB3417
36AB3533
37AB3611
38AB377
39AB381
40AB39DODO
41AB4017
42AB4137
43AB4237
44AB4337
45AB4433
46AB45ab7
47AB463n/a
48AB4737
49AB48ab7
50AB4973
51AB503n/a
52AB511n/a
53AB527n/a
54AB533n/a
55AB543n/a
56AB557n/a
57AB563n/a
58AB573n/a
59
60
Sheet798
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT((B2:B60=F1)*(ISNUMBER(C2:C60)))

Thanks a million. I guess if i want to do the opposite, I simply swap columns in the same code. You saved my *** and i really appreciate
 
Upvote 0
Thanks a million. I guess if i want to do the opposite, I simply swap columns in the same code. You saved my *** and i really appreciated. 1 problem though is that the code counts blank cells as zero
 
Upvote 0
1 problem though is that the code counts blank cells as zero
 
Upvote 0
So, are you saying you may use 0 as the Count criteria?

Is this what you mean, added another test for Blanks:

Book3.xlsx
ABCDEF
1CandidateTest 1Test 2Criteria0
2AB17n/aCount0
3AB23n/a
4AB33n/a
5AB43n/a
6AB537
7AB637
8AB737
9AB8ab7
10AB9ab3
11AB1023
12AB1117
13AB1237
14AB131DO
15AB14ab3
16AB15ab7
17AB1613
18AB17ab7
19AB18ab7
20AB1937
21AB20ab3
22AB21abDO
23AB22ab1
24AB23ab3
25AB24ab1
26AB25ab2
27AB2627
28AB2712
29AB2813
30AB291DO
31AB3033
32AB3113
33AB3233
34AB33ab1
35AB3417
36AB3533
37AB3611
38AB377
39AB381
40AB39DODO
41AB4017
42AB4137
43AB4237
44AB4337
45AB4433
46AB45ab7
47AB463n/a
48AB4737
49AB48ab7
50AB4973
51AB503n/a
52AB511n/a
53AB527n/a
54AB533n/a
55AB543n/a
56AB557n/a
57AB563n/a
58AB573n/a
590
60
Sheet798
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT((B2:B60=F1)*(B2:B60<>"")*(ISNUMBER(C2:C60)))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top