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

[TABLE="width: 581"]
<tbody>[TR]
[TD]Acct #[/TD]
[TD]Test[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Acct #[/TD]
[TD]Test Type[/TD]
[TD]Date Completed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]1/18/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]6/15/2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]9/6/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]8/15/2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]3/2/2017[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]DEF[/TD]
[TD]3/8/2016[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]JKL[/TD]
[TD]05/16/17[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]ABC[/TD]
[TD]7/1/2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]ABC[/TD]
[TD]5/11/2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]GHI[/TD]
[TD]06/04/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]ABC[/TD]
[TD]8/17/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]ABC[/TD]
[TD]7/6/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]GHI[/TD]
[TD]06/30/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]ABC[/TD]
[TD]7/28/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]JKL[/TD]
[TD]10/06/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]JKL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]ABC[/TD]
[TD]3/30/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]ABC[/TD]
[TD]3/30/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]ABC[/TD]
[TD]9/28/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]JKL[/TD]
[TD]11/26/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]DEF[/TD]
[TD]10/31/2017
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.