a more advanced countif?

Put the towns in a separate table assuming its say A2:B200 on sheet2
and the data is in sheet1 Columns A and B

now use
=COUNTIFS( Sheet1!A2:A200, Sheet2!A2, Sheet1!B2:B200, ">0")

Which version of Excel do you use?

Originally Posted by aaronp123

I'm using 2003 i'm afraid!!

You should update your profile. It may have prevented a page full of COUNTIFS suggestions

try

Formula:

=SUMPRODUCT(--(A1:A7="Birmingham"),--(C1:C7>0))

Not sure how do you want it to work. Try this to see if this is what you want?
Assuming that your data is in the range A2:C8, Where column A has site names and column C has amounts.

=COUNTIFS(A2:A8,"*Birmingham*",C2:C8,"<>")

Change the formula as per your need.

In that case you may try Sumproduct like this......

=SUMPRODUCT(--(ISNUMBER(SEARCH("Birmingham",A2:A8))),--(C2:C8<>""))

Originally Posted by aaronp123

when i try this i am getting #Value, should i be entering anything in place of isnumber or search?

The formula Assumes that your data (example in post#1) is in the range A2:C8 with row 1 as column header. Where Col. A has Sites, Col. B has Names and Col. C has amounts.
If still you get the error, please upload a sample workbook by clicking on Go Advanced below the Quick Reply Box and finding the paper clip icon to upload the sample workbook by following simple instructions.

You can also try

Formula:

=SUM((A1:A7="Birmingham")*(C1:C7<>""))

This is an array formula so you would need to press CTRL + Shift + Enter instead of just Enter or Tab

If you are going to be lax on capitalization as you were in the sample you provided it may help to use

Formula:

=SUM((UPPER(A1:A7)="BIRMINGHAM")*(C1:C7<>""))

edit: My formulas assume the same data layout as sktneer mentioned above.

So in your example file, use formula:

Formula:

=SUMPRODUCT(--(A:A="Aldershot"),--(B:B>0))