# howto create Iterative dynamic ranges

#### OKTileguy

##### New Member
Hi,

I am trying to find a way to count unique values in a list, based on multiple criteria. The problem is, the list is thousands of rows long, and i need to count the unique values for EACH of several products, and each product will have a different range. I assume I have to somehow combine SUMIF and dynamic ranges, but I'm stumped.

Specifically, my data is like this:

Product Run Color

A 1 Red

A 1 Blue

A 1 Green

B 1 Red

B 1 Green

A 2 Orange

A 2 Red

C 1 Blue

I need to know the number of different colors I made for EACH product, in EACH separate run, i.e. product A, run 1, 3 colors; product A run 2, 2 colors; etc.

I have MANY different products, each with MANY separate runs, and each run may have anywhere from 1-10 different colors.

#### kchiba

##### Active Member
Hi OKtileguy,

Have you tried using a pivot table, or a filter with subtotal.

cheers

kanti

#### Hui

##### Excel Ninja
Staff member
You can setup a manual pivot table with say Products on the Left, Colors across the Top and a cell for Run and then populate the matrix easily with Sumproduct

eg =sumproduct((ColA=Product)*(ColC=Color)*(ColB=Run))

where

Product is a list of products down the left side

Color is a list of colors across the top

and Run is the cell with the Run NO.

#### dan_l

##### Active Member
Hui's right. Create a cross tab of some sort and sum product.

Though, if this is sufficient size, this might be a job for access/base/whatever.

#### OKTileguy

##### New Member
Hui, thanks for the input, but what is a "manual" pivot table? I have managed to get the information I needed by doing a pivot table, with product, run number, and color all on the side, then counting the number of colors in each run with countif, but this is not a good solution for changing date, since I'll have to copy and paste the pivot table results everytime the information is updated.

#### dan_l

##### Active Member
A manual pivot table would be you manually creating the table and manually writing the formula.

It's very manual.

#### kchiba

##### Active Member
You can do the following:

Prod Run Colour

A 1 RED

A 1 GREEN

A 1 BLUE

B 1 RED

B 1 GREEN

A 2 ORANGE

A 2 RED

C 1 BLUE

Prod Run Red Green Blue Orange

A 1 1 1 1 0

A 2 1 0 0 1

B 1 1 1 0 0

C 1 0 0 1 0

In the second block above Prod is input, Run is input and below each of the colours you have the sumproduct formula.

If the top block is in A1:C9 and the second block is in A11:F15

Insert the following formula under Red :=SUMPRODUCT(--(\$A\$2:\$A\$9=\$A12),--(\$B\$2:\$B\$9=\$B12),--(\$C\$2:\$C\$9=C\$11)) and copy it down and across.

You still need to create the list of Products and runs manually.

Hope this helps,

kanti

#### Hui

##### Excel Ninja
Staff member
OKTileguy

When I say Manual Pivot Table it will look like a Pivot Table but will not automatically work out new column/row headers, but it will update automatically

You will need to populate the Row and Column Labels and put all the formulas in

In your case where you have Column A=Product, Column B=Run & Column C = Color

off to the right of your data

Add a list of Products down a Column Say M3:M100

Add a list of Runs across the Top Say N2:Z2

Add a cell to list Color Say N1

Then in N3 use

=SUMPRODUCT((\$A\$2:\$A\$1000=\$M3)*(\$B\$2:\$B\$1000=N\$2)*(\$C\$2:\$C\$1000=\$N\$1))

and copy N3 to fill your matrix

You can use Named Ranges to simplify the equations

eg: =Sumproduct((Product=\$M11)*(Run=N\$10)*(Color=\$N\$9))

I have posted a sample of the above at

http://rapidshare.com/files/424507806/OKTileguy.xlsx

which also includes a more complex layout with all 3 variables displayed at all times