Count of Unique Rows Using Multiple Criteria Ignoring Blanks in Data

[paulrh]

From the following example data set I would like to count the number of rows where "Company A" and the word "Completed" appear but not counting duplicate values in the first column and ignoring the blanks that might appear in the first column. It is ignoring the blanks I am having difficulties with.


[TABLE="width: 402"]
<tbody>[TR]
[TD]WL-ADB-190229[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADM-190232[/TD]
[TD]Company M[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190238[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190238[/TD]
[TD]Company A[/TD]
[TD]Non-job[/TD]
[/TR]
[TR]
[TD]WL-ADB-190229[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company Z[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company Z[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company M[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company M[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company M[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company Z[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]n/a[/TD]
[TD]Company M[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]WL-ADB-190302[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190303[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190301[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190231[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190305[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190306[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADC-190307[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADC-190307[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company M[/TD]
[TD]Unit Rental[/TD]
[/TR]
[TR]
[TD]WL-ADZ-190237[/TD]
[TD]Company Z[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190312[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190309[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190312[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]WL-ADB-190309[/TD]
[TD]Company A[/TD]
[TD]Completed[/TD]
[/TR]
</tbody>[/TABLE]


Thank you

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1
2	WL-ADB-190229	Company A	Completed		11
3	WL-ADM-190232	Company M	Completed
4	WL-ADB-190238	Company A	Completed
5	WL-ADB-190238	Company A	Non-job
6	WL-ADB-190229	Company A	Completed
7	n/a	Company Z	Unit Rental
8	n/a	Company Z	Unit Rental
9	n/a	Company M	Unit Rental
10	n/a	Company M	Unit Rental
11	n/a	Company M	Unit Rental
12	n/a	Company Z	Unit Rental
13	n/a	Company M	Unit Rental
14	WL-ADB-190302	Company A	Completed
15	WL-ADB-190303	Company A	Completed
16	WL-ADB-190301	Company A	Completed
17	WL-ADB-190231	Company A	Completed
18	WL-ADB-190305	Company A	Completed
19		Company A	Completed
20	WL-ADB-190306	Company A	Completed
21	WL-ADC-190307	Company A	Completed
22	WL-ADC-190307	Company A	Completed
23		Company M	Unit Rental
24	WL-ADZ-190237	Company Z	Completed
25	WL-ADB-190312	Company A	Completed
26	WL-ADB-190309	Company A	Completed
27	WL-ADB-190312	Company A	Completed
28	WL-ADB-190309	Company A	Completed
Sheet

 

[paulrh]

Try:

Spreadsheet Formulas

Cell Formula E2 {=SUM(IF(FREQUENCY(IF($B$2:$B$28="Company A",IF($C$2:$C$28="Completed",IF($A$2:$A$28<>"",MATCH($A$2:$A$28,$A$2:$A$28,0)))),ROW($A$2:$A$28)-ROW($A$2)+1),1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

This works perfectly. Thank you so much.

 

[AhoyNC]

You're welcome. Thanks for the feedback.