# Count Exclusive Rows According to Conditionals

#### rsbuzatto

##### Board Regular
Hello folks,

I'm working in a huge workbook of works/opportunities registration. The employees use this workbook to fill a couple of rows about a new work/opportunity (idea) that they have. Thus, I need to develop a new way to improve the management of the datas which are inputted there.

• Manage all the Inputted Opportunities:
According to the employee area and the opportunity inputted month, I need to count how many EXCLUSIVE opportunities I had with this two conditions. (I can use =SUMPRODUCT(1/COUNTIF(xxx;xxx)) formula, however, there are two conditions to use and I'm not able to modify the formula to this purpose).

PS:
Employee Area: Area 1 (Column F in my workbook)
Inputted Month: 1 (Column X
in my workbook)

The macro have to count how many exclusive opportunities happened (Column H
in my workbook) in January in the Area 1, for example.

• Manage all the Implemented Opportunities:
​The second part is more difficult than the first part above, I guess. I need to manage all the implemented opportunities which were inputted in the workbook. According to the employee area and the opportunity implemented month, I need to count how many EXCLUSIVE opportunities I had with this two conditions.

PS:
Employee Area: Area 2 (Column F in my workbook)
Implemented Month: 2 (Column Y
in my workbook)

I know whether an opportunity is implemented or not according to the columns L to T (depending on the implemented way, there is a different point to the employees, that's why the worksheet has 9 columns for that). Thus, I have my Column H (work/opportunities titles) and Columns L to T (marked with a "X" indicating the employee implemented way for the opportunity).

So, the macro must count how many exclusive data appears.

To be easier for you guys, follow some picture links below.

Image 1: http://img341.imageshack.us/img341/489/73821128.jpg
Image 2: http://img29.imageshack.us/img29/1528/48173619.jpg

That's all!

Thanks very much and any help would be very helpful.
I'm not too familiar with VBA, that's why I'm in a trouble...

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi

First part

I need to count how many EXCLUSIVE opportunities I had with this two conditions.

See if the example below helps (using formulas, no VBA needed)

A B C D E
 Area Month Month Count Unique Area 1 1 1 3 Area 2 1 2 4 Area 1 1 Area 3 1 Area 1 1 Area 2 1 Area 1 2 Area 2 2 Area 1 2 Area 2 2 Area 3 2 Area 4 2

<tbody>
</tbody>

Array formula in E2
=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$100=\$D2,MATCH(\$A\$2:\$A\$100,\$A\$2:\$A\$100,0)),ROW(\$A\$2:\$A\$100)-ROW(\$A\$2)+1),1))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift Keys and hit Enter)

copy down

Ajust the ranges to your real case.

By the way, try to post your data in the forum reply page (is not possible to copy your data from an image for testing purposes)

Do to this:
Select a relevant part of your data, say 10 to 15 rows, including headers
Put borders
Copy (Ctrl+C)
Right-click in the forum reply page and pick Paste

M.

Replies
1
Views
368
Replies
13
Views
424
Replies
10
Views
989
Replies
8
Views
655
Replies
3
Views
366

Threads
1,203,553
Messages
6,056,063
Members
444,841
Latest member
SF_Marnie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back