Counting number of unique numbers based on another variable.

Hi All,

I have a problem that I've been trying to resolve but just cannot find my answer.

I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.

I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.

However I cannot for the life of me work out how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) and it's driving me insane.

Would someone be able to assist me?

Regards

Hi,

You can use a PIVOT Table instead, please check the attached file if this is what you were looking for.

Regards,
Chandra


Please click on ‘ * Add Reputation’ button on the left side bottom of my post if I was helpful in resolving the issue.

Hi Chandra

Thanks for your reply. I am looking to avoid using a pivot table for this purpose if possible. Normally I'm a big fan of using them.
Is what I want to achieve possible using only a formula?

Regards

Pl see attached file with formula.

Hi,

I would first like to apologize to all Moderators and Gurus if I'm breaking any of the forum rules here, but I just got curious and wanted to know more about the following.

While searching across this forum, I came across the below mentioned thread in which I found a formula similar to this requirement in this thread.

Apply a formula in a new column that pulls unique values from another column

I explored the sample workbook posted by the user in the below mentioned thread and just wondered how this unique counting works well through INDEX and MATCH for Country names (Text data) in an array formula whereas in this thread, the same logic does not work out for picking out unique PO# (numeric data).

The user has used an array formula with a named range CountryList which refers to the list of countries in a column (similar to PO# in this case) and also a data connection Table_css_database_mv_cosl_data_2014DSAProgress[countryName] that fetches the entire data present in the sheet.

The array formula used is as below and it fetches all the unique country names :

Please Login or Register  to view this content.

Could somebody throw light on this?


Sarang

Bumping thread due to no response..

Originally Posted by kvsrinivasamurthy

Pl see attached file with formula.

Hi kvsrinivasamurthy,

Thanks, your solution works well on the sample data. The size of the data is actually different each time, is there anyway to have this work without knowing how many rows there are in the data sheet?

Best Regards

Pl see attached file. It takes care of all the rows in the column irrespective of number of Rows.

Hi kvsrinivasamurthy,

Perfect, that works exactly as I am trying to do. Although I don't quite understand what the formula is actually doing. Another quick one if you dont mind. Assuming that the L column has currency values, is it possible to do a sum based on the product just the same way as the frequency? So for example, it would sum up all the EQP CONSUMABLES values and show the $value of them.

Regards

Pl see file.

Hi,

Srinivas solution works perfectly..

One small correction though which I noted for calculating currency values (from col L) is

Instead of using static formula :

Please Login or Register  to view this content.

We can use (similar to counting unique PO# and using array formulas [confirmed] by pressing CTRL + SHIFT + ENTER ) :

Please Login or Register  to view this content.

Sarang

Thanks Saarang84. Sorry for the mistake I did.

Thanks very much for your help kvsrinivasamurthy and saarang84. All problems solved and I have my report doing exactly what I want.

Regards