# formula to toal numbers depending on criteria

#### Andy83UK

##### Board Regular
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try:

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

deleted, better solution already posted.

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.

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

Cheers

Replies
11
Views
199
Replies
4
Views
250
Replies
10
Views
217
Replies
1
Views
364
Replies
5
Views
269

1,211,454
Messages
6,101,947
Members
447,765
Latest member
bhutta5437

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

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