"Dynamic" range for COUNTIF function

rcataldo

New Member
Joined
Mar 1, 2005
Messages
10
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top