CountIf with exceptions?

Gary McMaster · Jun 22, 2011

Hi Everyone,

Given the sample below, how can I count how many times a given letter appears in the table with a number (or non-blank cell) in the cell to its right. In this example the answer is A=3, B=2 and everything else = 4.

I'm not having much luck trying it with CountIf. Is there something better?

Thanks for any advice.

Gary

Excel Workbook

B

C

D

E

2

A

1

B

2

3

C

3

D

4

E

5

F

6

5

G

7

H

8

6

I

9

J

10

7

K

11

L

12

8

B

2

A

1

9

D

4

C

3

10

F

6

E

5

11

H

8

G

7

12

J

10

I

9

13

L

12

K

11

14

A

B

15

C

3

D

4

16

E

5

F

6

17

G

7

H

8

18

I

9

J

10

19

K

11

L

12

20

B

A

1

21

D

4

C

3

22

F

6

E

5

23

H

8

G

7

24

J

10

I

9

25

L

12

K

11

Sheet1

Robert Mika · Jun 22, 2011

Excel Workbook

A

B

C

D

E

F

1

A

1

B

2

*

2

C

3

D

4

*

2

3

E

5

F

6

*

3

4

G

7

H

8

*

5

I

9

J

10

*

6

K

11

L

12

*

7

B

2

A

1

*

8

D

4

C

3

*

9

F

6

E

5

*

10

H

8

G

7

*

11

J

10

I

9

*

12

L

12

K

11

*

13

A

*

B

*

14

C

3

D

4

*

15

E

5

F

6

*

16

G

7

H

8

*

17

I

9

J

10

*

18

K

11

L

12

*

19

B

A

1

*

20

D

4

C

3

*

21

F

6

E

5

*

22

H

8

G

7

*

23

J

10

I

9

*

24

L

12

K

11

*

Sheet1

mikerickson · Jun 22, 2011

Try
=SUMPRODUCT(--(B2:D25="A")*ISNUMBER(C2:E25))

Gary McMaster · Jun 22, 2011

Thanks Mike and Robert. I was able to slightly modify your formulas to fit my real data and it seems to be working.

Gary

Aladin Akyurek · Jun 22, 2011

Gary McMaster said:
Hi Everyone,

Given the sample below, how can I count how many times a given letter appears in the table with a number (or non-blank cell) in the cell to its right. In this example the answer is A=3, B=2 and everything else = 4.

I'm not having much luck trying it with CountIf. Is there something better?

Thanks for any advice.

Gary

Excel Workbook
B C D E
2 A 1 B 2
3 C 3 D 4
4 E 5 F 6
5 G 7 H 8
6 I 9 J 10
7 K 11 L 12
8 B 2 A 1
9 D 4 C 3
10 F 6 E 5
11 H 8 G 7
12 J 10 I 9
13 L 12 K 11
14 A B
15 C 3 D 4
16 E 5 F 6
17 G 7 H 8
18 I 9 J 10
19 K 11 L 12
20 B A 1
21 D 4 C 3
22 F 6 E 5
23 H 8 G 7
24 J 10 I 9
25 L 12 K 11
Sheet1

F2: A
F3: B
Etc.

G2, control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$C$25=F2,IF(ISNUMBER($B$2:$D$24),1)))

Aladin Akyurek · Jun 22, 2011

Aladin Akyurek said:
F2: A
F3: B
Etc.

G2, control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$C$25=F2,IF(ISNUMBER($B$2:$D$24),1)))

Edit for range...

=SUM(IF($A$2:$C$25=F2,IF(ISNUMBER($B$2:$D$25),1)))

Gary McMaster · Jun 23, 2011

Thanks Aladin. I'm trying all of these suggestions. I already have this working using VBA code but I am trying to learn more about formulas. I'm still studying the formulas already posted to try and understand them. There is one more part to this that I'm hoping can also be done with formulas.

Below is what I have so far. This uses the same data shown in my original post. For each identifier in columns B & D, I would like to count the number of times its associated value (column C or D) is greater than, less than or equal to its counterpart in the other ID column. The formula I used in column J (which seems to work) is my own feeble attempt ... maybe there is a better way to do that too.

Thanks very much for all the replies so far.

Gary

Excel Workbook