Count Exclusive Rows According to Conditionals

[rsbuzatto]

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

 

[Marcelo Branco]

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.