Total Entries between Two Dates in Table

Hi,

I'm an Excel newbie using 2010, but have been given a rather daunting task that I could use some help with!

I have a lot of data organised in a table, something such as:

Column A: Name, Column B: City, Column C: Date Entered

I can obviously filter this table to show the values I need, but I need it to automatically tell me the total amount of people entered per week, per city on a separate worksheet.

I understand I could use a pivot table for some of this, but I want to further automatically manipulate the data once I have the totals (for example, adding a financial value for each, etc) which I'm not certain how to do with a pivot.

So, ideally what I would like is a formula that says total entries in Table 1 that are marked Sheffield in Column B and have a value including or between 01/03/2014 and 07/03/2014 in Column C.

I'm really grateful for any help you can offer me.

You can use COUNTIFS for this. List your Cities across in row 1, starting with B1. Put the first date in A2 and this formula in A3:

=A2+7

then copy this down as far as you need to, then in B2 you can have this formula:

=COUNTIFS(Sheet1!$B:$B,B$1,Sheet1!$C:$C,">="$A2,Sheet1!$C:$C,"<"&$A2+7)

then copy across and down as required.

Hope this helps.

Pete

Thank you, Pete, this looks to be the right sort of thing!

If I want the formula is specify just one city, is there a way to put the city name into the formula, rather than identify a separate cell with the value entered?