Countifs and Isblank??

[mg4654]

I am trying to create a task list of pending tests on 1 main worksheet from another worksheet with multiple account numbers for the same person.

I need to match the account number (which is repeated multiple times on the worksheet I am pulling from) to a column that may or may not have a date. If a date is present it means the test is completed and if not the test is pending. When done I only want to show if any tests are "Pending". I don't want to show zero's or any other values. Every formula I try comes up with too many arguments.

Here's what I am working with

Acct #	Test				Acct #	Test Type	Date Completed
1					1	ABC	1/18/2018
2					1	ABC	6/15/2017
3					2	ABC	9/6/2018
4					2	ABC	8/15/2017
5					3	ABC	3/2/2017
6					3	DEF	3/8/2016
7					3	JKL	05/16/17
8					4	ABC	7/1/2018
9					4	ABC	5/11/2018
10					4	GHI	06/04/18
					5	ABC
					5	ABC	8/17/2018
					6	ABC	7/6/2018
					7	GHI	06/30/17
					9	ABC
					9	ABC	7/28/2017
					9	JKL	10/06/17
					9	JKL
					10	ABC
					10	ABC	3/30/2017
					10	ABC	3/30/2017
					10	ABC	9/28/2017
					10	JKL	11/26/17
					10	DEF	10/31/2017

<tbody>
</tbody>

 

[Fluff]

Try

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Acct #	Test				Acct #	Test Type	Date Completed
2	1					1	ABC	18/01/2018
3	2					1	ABC	15/06/2017
4	3					2	ABC	06/09/2018
5	4					2	ABC	15/08/2017
6	5	Pending				3	ABC	02/03/2017
7	6					3	DEF	08/03/2016
8	7					3	JKL	16/05/2017
9	8					4	ABC	01/07/2018
10	9	Pending				4	ABC	11/05/2018
11	10	Pending				4	GHI	04/06/2018
12						5	ABC
13						5	ABC	17/08/2018
14						6	ABC	06/07/2018
15						7	GHI	30/06/2017
16						9	ABC
17						9	ABC	28/07/2017
18						9	JKL	06/10/2017
19						9	JKL
20						10	ABC
21						10	ABC	30/03/2017
22						10	ABC	30/03/2017
23						10	ABC	28/09/2017
24						10	JKL	26/11/2017
25						10	DEF	31/10/2017
Settings

Cell Formulas
Range	Formula
B2	=IF(COUNTIFS($H$2:$H$25,"",$F$2:$F$25,A2)>0,"Pending","")

 

[DENomad]

Another method -

=IFERROR(IF(INDEX($G$2:$G$25,MATCH(A2,$E$2:$E$25,0))="","Pending","Completed"),"No Account Number")

 

[mg4654]

Thank you!!!! Formula works perfectly.
Any way to speed up processing speed.....takes a while to calculate multiple formulas across spreadsheets.

 

[Fluff]

Glad we could help & thanks for the feedback.
As for speeding things up, it depends on the layout of your workbook and what formulae you are using.