Formula help needed

[bcowans]

So, I have a several locations. Lets say Phoenix, Goodyear, Avondale.
For each location, they could have a number; 1, 2, 3, 4, or 5.
Keep in mind, the location and numbers can be listed multiple times on this sheet.
So what I need is, for the Location of Phoenix, what is the total amount for 1s and 2s and 3s and so on.
I would need to do this for each location.

 

[JimM]

Sounds like a pivot table would be best bet but if you really want to do it by formula then

Assuming location in col A, number in column B

Then the No1s for Phoenix would be

=countifs(A:A,"Phoenix",B:B,1)

 

[Special-K99]

This should work (untested)

So Sheet1 has two columns: Location (Column A) and Number 1-5 (Column B)

in Sheet2!A2:A4 put Phoenix then Goodyear then Avondale into the cells.
in Sheet2!B1:F1 put 1 2 3 4 5 in the cells.
So you have a grid, column A lists locations, row 1 lists the numbers to total up.

in Sheet2!B2
=SUMIFS(Sheet1!B$1:B$1000,Sheet1!A$1:A$1000,$A2,Sheet1!B$1:B$1000,b$1)
copy across and down the grid