Calculate total sales tax per county across different rows and columns

[davilillo]

I have a spreadsheet with sales tax collection data for 2018's Q4. It includes which county specifically each order's sales tax was collected for. See below:



I need to calculate the total sales tax collected per county in orders shipped to California in column M. To accomplish that, I’d need a formula that:

1. Searches for the name of the corresponding county in column L on column G, in the same row where "County" appears on column F
2. When it finds the county name, verifies that text "CA" appears two rows above, next to "State" (to prevent duplicates with other states)
3. Adds up all the "Total_Tax" values next to CA for that county

Thanks a lot in advance!

 

[Marcelo Branco]

Maybe this...

=SUMIFS(H$2:H$1000,F$2:F$1000,"State",G$2:G$1000,"CA",F$4:F$1002,"County",G$4:G$1002,L2)

M.

 

[davilillo]

Thanks Marcelo; your formula seems to be spot on!

 

[Marcelo Branco]

You're welcome. Glad to help.

M.