# Formula help needed

#### bcowans

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

1,084,733
Messages
5,379,498
Members
401,607
Latest member
Zemexi

### This Week's Hot Topics

• VBA code giving errors and stopping Excel
Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
• Disable MsgBox message
Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
• Macro Recorder into VBA, Copy Paste Data Filled Cells
Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
• Number format changes while pasting into a cell
Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
• Collating data
Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
• Sum Multiple Columns Based on Multiple Criteria
I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...