Formula help needed

bcowans

New Member
Joined
Oct 19, 2018
Messages
4
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
Joined
Nov 11, 2003
Messages
610
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
Joined
Nov 7, 2006
Messages
8,328
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
 

Forum statistics

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

Some videos you may like

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...
Top