I am trying to make a formula that looks at a large data set of number and text values in a single column, and then only adds them up if they meet a criteria in another column.
For example:
Column Q could contain 500 rows most of which are numbers, some are text, and I want to total up only the numbers, BUT I only want to total the numbers in this column if coloumn G meets a certain criteria, usually the name of a company, and the numbers referring to customers with that company.
So far I've tried several variations on the formula below
{=SUBTOTAL(IF(9,'APRIL 2010 DATA'!$Q$3:$Q$999)*('APRIL 2010 DATA'!$G$3:$G$999=A5))}
Thanks for your help
For example:
Column Q could contain 500 rows most of which are numbers, some are text, and I want to total up only the numbers, BUT I only want to total the numbers in this column if coloumn G meets a certain criteria, usually the name of a company, and the numbers referring to customers with that company.
So far I've tried several variations on the formula below
{=SUBTOTAL(IF(9,'APRIL 2010 DATA'!$Q$3:$Q$999)*('APRIL 2010 DATA'!$G$3:$G$999=A5))}
Thanks for your help