"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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

IrishDave

New Member
Joined
Dec 14, 2004
Messages
29
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

Active Member
Joined
Dec 22, 2004
Messages
359
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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
Top