Forum Discussion

Brass Contributor

Mar 13, 2023

Which feature should I use for a sum of taxes collected for each city by name?

Hi all, Your help is always appreciated, greatly. I would like to add the sum of taxes collected for each city on a spreadsheet ... I have no idea of how to go about that. Here is a pic of my c...

excel

Formulas and Functions

HansVogelaar

MVP

Mar 13, 2023

johnsboxftm

In B30:

=SUMIF($B$2:$B$26, A30, $G$2:$G$26)

Fill down.

Alternatively, create a pivot table based on the data, and add the City field to the Rows area and the Tax field to the Values area.

johnsboxftm

Brass Contributor

Mar 13, 2023

I do have a table created with all the cities I have tax rates for ... I was hoping to have the formula automatically pull from that table's list of cities, then look at which of those cities I have on this particular sheet and then give me a grand total of $ in taxes

HansVogelaar
MVP
Mar 13, 2023
johnsboxftm

A pivot table based on the data range in your first screenshot will automatically list only the cities that are present in that range.
- johnsboxftm
  Brass Contributor
  Mar 13, 2023
  HansVogelaar
  
  I apologize, but I am not comprehending.
  If I could pull from this list:
  Any of these cities that appear in this column:
  
  Have each city by name appear in A30 and so on (where if there are 11 Clevelands it just says Cleveland) and in Box B30 be the total for those Clevelands taken from G3 and down
  - HansVogelaar
    MVP
    Mar 13, 2023
    johnsboxftm
    
    Select B2:H26 on the February 2023 sheet.
    
    On the Insert tab of the ribbon, click PivotTable.
    
    Select 'Existing Worksheet'.
    
    Click in the Location box.
    
    Click in cell A29, then click OK.
    
    Excel should display the PivotTable Fields task pane.
    
    Drag City to the Rows area.
    
    Drag Tax to the Values area.