Summarize data with multiple conditions
 

I have a dataset with basically 3 columns of data of interest. 2 columns
contain conditions (location and month), and the 3rd column contains a
numerical value (hrs). The worksheet has a header row, and the data starts
right below (no blanks). I want to build an equation to sum the hrs for a
specific month and place. The equation below works as long as I know how many
rows of data I have:

=SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25))

Unfortunately, Data will be added on a daily basis, so I don't know how many
rows are in the range. If I just select the column it gives me an error
message. Is there a trick that will allow me to accomplish this without
having to modify the equation routinely? Not all the users of the
spreadsheet are Excel savvy.

Thanks

You could simply use a larger range like

B5:B65535

or better

B5:B5000

jsut use something that never will be filled

or you can use a dynamic range

http://www.contextures.com/xlNames01.html#Dynamic


Regards,

Peo Sjoblom


"OkieViking" wrote:

I have a dataset with basically 3 columns of data of interest. 2 columns
contain conditions (location and month), and the 3rd column contains a
numerical value (hrs). The worksheet has a header row, and the data starts
right below (no blanks). I want to build an equation to sum the hrs for a
specific month and place. The equation below works as long as I know how many
rows of data I have:

=SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25))

Unfortunately, Data will be added on a daily basis, so I don't know how many
rows are in the range. If I just select the column it gives me an error
message. Is there a trick that will allow me to accomplish this without
having to modify the equation routinely? Not all the users of the
spreadsheet are Excel savvy.

Thanks