Count cells based on data range

[USCguy09]

I have been trying to come up with 1 equation that will count certain text for a given range that's based off of a data range but to no avail.

I thought =countif(i2:i12,Status_Name) after naming array N2:N10 as Status_Name would work but it doesn't. Looking at the photo below, say the equation is to go in cell M2. I need a formula that will look in array I2:I12 and count the cells that only match whatever is in array N2:N9. The correct count should be 8. Any help would be appreciated.

 

[njimack]

Excel Workbook
	I	J	K	L	M	N
1	status_name				Count	Status_Name
2	Cash				8	Cash
3	Cash					Financed
4	Financed					Pending
5	Financed					Unavailable
6	Pending					Note Sale
7	Pending					Note Sale w/Hold
8	Financed					Repossessed
9	Financed					Rented
10	No
11	No
12	No
Sheet3

#VALUE!

</td></tr></table></td></tr></table>

 

[USCguy09]

njimack,

That worked perfectly. I have 2 more questions if you don't mind.
If I were to incorporate an additional column where it says canceled,
I need an equation where now it will only count the cells where it matches Status_Name AND does not contain "Y" in column J. Correct answer would be 5. See changes made below.

Also, could you elaborate on what the two dash symbols in your equation actually perform?

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>status_name</td><td> Canceled</td><td>
</td><td>
</td><td>Count</td><td>Status_Name</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Cash</td><td> Y</td><td>
</td><td>
</td><td style="background-color: rgb(255, 255, 0); text-align: right;">5</td><td>Cash</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Cash</td><td> Y</td><td>
</td><td>
</td><td>
</td><td>Financed</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>Financed</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Pending</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>Financed</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Unavailable</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>Pending</td><td> Y</td><td>
</td><td>
</td><td>
</td><td>Note Sale</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>Pending</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Note Sale w/Hold</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>Financed</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Repossessed</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>Financed</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Rented</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>No</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>No</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>No</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>M2</td><td>
</td></tr></tbody></table></td></tr><tr><td><table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Names in Formulas </td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Name</td><td>Applies to</td></tr><tr><td>M2</td><td>Status_Name</td><td>=Sheet3!$N$2:$N$9</td></tr></tbody></table></td></tr></tbody></table></td></tr></tbody></table>

 

[njimack]

=SUMPRODUCT(--(ISNUMBER(MATCH(I2:I12,Status_Name,0))),--(J2:J12<>"Y"))

The 2 minus signs convert an array of TRUEs and FALSEs into 1's and 0's. This could also be achieved by multiplying by 1, or adding 0.

 

[USCguy09]

Awesome! Thanks a bunch.

=SUMPRODUCT(--(ISNUMBER(MATCH(I2:I12,Status_Name,0))),--(J2:J12<>"Y"))

The 2 minus signs convert an array of TRUEs and FALSEs into 1's and 0's. This could also be achieved by multiplying by 1, or adding 0.