• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.


Any ideas? Thanks in advance.
 

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
 
Top