formula to toal numbers depending on criteria

[Andy83UK]

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

 

[Andrew Poulsom]

Try:

=SUMIF('APRIL 2010 DATA'!$G$3:$G$999,A5,'APRIL 2010 DATA'!$Q$3:$Q$999)

 

[jasonb75]

deleted, better solution already posted.

 

[Andy83UK]

Thats cracked the main of it thanks! =)

is there a way to add multiple criteria to count though as that would be the next step.

so instead of reffering to A5 and 1 business it would be refferring to and counting up several.

 

[Andrew Poulsom]

Yes it's possible with SUMPRODUCT (or SUMIFS if you have Excel 2007 or above).

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Andy83UK]

thanks its 2000 but i will use sumproduct I've found a work'around for now though. So problem solved!

Cheers