Formula Help Please

[rstagman]

Good morning. I'm a new user to this forum but have been using it for a while now to learn and expand my skill set on different formulas. With that being said, I'm stumped on this one. Here's what I'm trying to do with the data below. If the Column D equals "Monitoring" then I want to count unique values in Column B. The formula should only be returning a count of 1 since the Testing Name in Column B is the same even though there are 2 entries for monitoring in Column D. I tried writing the formula below but am not having any luck. I also need this formula to work for blank cells. Can someone have a double look at the formula I've used below and let me know where I'm messing up? Thank you all very much for your help.


FORMULA USED:
=SUM(--(FREQUENCY(IF(B3:B31<>"",IF(D3:D31="Monitoring",MATCH(B3:B31,B3:B31,0))),ROW(B3:B31)-ROW(B3)+1)>0))

DATA:

Ref #	Testing Name	Business Area	Testing Type
1	Testing 1	Corporate	Testing
2	Testing 2	Corporate	Testing
3	Testing 3	Corporate	Testing
4	Testing 4	Corporate	Testing
5	Testing 5	Corporate	Testing
6	Testing 6	Corporate	Testing
7	Testing 7	Corporate	Testing
8	Testing 8	Corporate	Testing
9	Testing 9	Corporate	Testing
10	Testing 10	Corporate	Testing
11	Testing 11	Corporate	Testing
12	Testing 12	Corporate	Testing
13	Testing 13	Corporate	Testing
14	Testing 14	Corporate	Testing
15	Testing 15	Corporate	Testing
16	Testing 16	Corporate	Testing
17	Testing 17	Corporate	Testing
18	Testing 18	Corporate	Testing
19	Testing 19	Corporate	Testing
20	Testing 20	Corporate	Testing
21	Testing 21	Corporate	Testing
22	Testing 22	Corporate	Testing
23	Testing 23	Corporate	Testing
24	Testing 24	Corporate	Testing
25	Testing 25	Corporate	Testing
26	Testing 26	Corporate	Testing
27	Testing 27	Corporate	Testing
28	Testing 28	Corporate	Testing
29	Testing 29	Commercial Lending	Monitoring
30	Testing 29	Corporate	Monitoring

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[rstagman]

I've been continuing to work on this since I've posted. all I needed to do was press Ctrl+Shift+Enter and it worked. Sorry for the wasted thread.

 

[AhoyNC]

Your formula worked for me.
It is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1
2	Ref #	Testing Name	Business Area	Testing Type
3	1	Testing 1	Corporate	Testing		1
4	2	Testing 2	Corporate	Testing
5	3	Testing 3	Corporate	Testing
6	4	Testing 4	Corporate	Testing
7	5	Testing 5	Corporate	Testing
8	6	Testing 6	Corporate	Testing
9	7	Testing 7	Corporate	Testing
10	8	Testing 8	Corporate	Testing
11	9	Testing 9	Corporate	Testing
12	10	Testing 10	Corporate	Testing
13	11	Testing 11	Corporate	Testing
14	12	Testing 12	Corporate	Testing
15	13	Testing 13	Corporate	Testing
16	14	Testing 14	Corporate	Testing
17	15	Testing 15	Corporate	Testing
18	16	Testing 16	Corporate	Testing
19	17	Testing 17	Corporate	Testing
20	18	Testing 18	Corporate	Testing
21	19	Testing 19	Corporate	Testing
22	20	Testing 20	Corporate	Testing
23	21	Testing 21	Corporate	Testing
24	22	Testing 22	Corporate	Testing
25	23	Testing 23	Corporate	Testing
26	24	Testing 24	Corporate	Testing
27	25	Testing 25	Corporate	Testing
28	26	Testing 26	Corporate	Testing
29	27	Testing 27	Corporate	Testing
30	28	Testing 28	Corporate	Testing
31	29	Testing 29	Commercial Lending	Monitoring
32	30	Testing 29	Corporate	Monitoring
Sheet

 

[rstagman]

Thank you, AhoyNC! I was not hitting Ctrl+Shift+Enter