"Dynamic" range for COUNTIF function

[rcataldo]

Hi all,

Is it possible to create a dynamic range based on the value of a cell? For example, I am trying to do something like this:

=COUNTIF(A1:A(insert cell value here), "Blue")

So my range would be A1 to A(x) where x is the number value of a predetermined cell.

This would allow my range to change dynamically depending on the value of a specific cell. Is there anyway to accomplish this?

What I am doing is this... I have a 2 column table of data. Column 1 is "Product Type" and column 2 is "Color". There are four different products and five different available colors. I need to count the number of one type of product in each color. I can easily get a total of each product, or each color, but not a total of say Product A in blue. Any recommendations?

 

[IrishDave]

Have a crack at this

=COUNTIF(INDIRECT("A1:A"& B1),"Blue")

just replacing B1 with the cell you wish to contain the numerical reference of the number of rows.

 

[anubis]

You could also use a sumproduct:

=SUMPRODUCT((A1:A1000="sedan")*(B1:B1000="white"))

Though this will not give you the dynamic range you need, it will return the total of Product A in specified color.

 

[PaddyD]

or something like:

=COUNTIF(A1:INDEX(A:A,D1),"Blue")