Interesting formula I have not used before.
It returns the state piece of 46.73 for salt lake city. However, how can I
use this to identify that the Utah is for salt lake or how I can find all of
the salt lake city groups within the 10,000 lines to total them together.
There of course is many different cities in Utah.

This has gotten me thinking though. Is there a formula that will look the
county city "Salt Lake" and pull all of the columns B with the city name of
Salt lake, have it return the invoice # and amount and type.

Then a formula that maybe match the invoice #'s from the Salt Lake list to
find the tax billed "Utah" and Taxalbe Sales "Utah" that corralates.

Or maybe I can use this formula just change the "Utah" to the city name and
could I have it also return column D as well as column F?

Two steps, and I would have to do this with every city/county name.

Thanks this is a good start. Thanks so much




"Barb Reinhardt" wrote:

Let's say your data is in the columns shown below:

A B C D E F
State - ype - County/City - Invoice # - Ref # - Amount
UT - Tax Billed - Salt Lake - 073714 - 238025 - $10.82
UT - Taxable Sales - Utah - 073714 - 238025 - $983.75
UT - Tax Billed - Slt Lk Cnty 073714 - 238025 - 4.92
UT- taxable Sales Slt Lk Cnty 073714 - 238025 - 983.75
UT - Tax billed - Utah - 073714 - 238025 - 46.73

I'm assuming your data is in rows 3-7 for this example.

Let's say we have the following values
B20: Tax Billed
C20: Utah
D20: =SUM(IF(B3:B7=B20,IF(C3:C7=C20,F3:F7)))
make sure you commit the array formula with CTRL SHIFT ENTER. It will
have {} around it when you are done.



"taxmom" wrote:

I'm sorry it is difficult to explain.

I need to see all of the data that is there. I just need to see grouped
with all sales tax pieces together. The sales tax pieces would include

Salt lake Taxable 500.00
Salt lake Deductible 200
Salt lake city state tax 5.00
Salt lake city city tax 10.00
total tax 15.00

Unfortunately, with this report it will tell you the taxable, deductible tax
billed for each line. A line is state piece, city piece, county piece. If
the invoice is not taxable then you would only see deductible city,
deductible UTAH, deductible county

sample of a 3 consistant taxable invoices

UT - Tax Billed Salt lake city - 073714 - 238025 - 10.00
UT - Tax Billed Salt Lake city - 651439 - 612345 - 10.00
UT - Tax billed Salt Lake City - 432159 - 437854 - 10.00

Total Tax Bille Salt Lake City 30.00

UT - Tax Billed - UTAH - 073714 - 238025 - 5.00 - state tax piece
UT - Tax Billed - UTAH - 651439 - 612345 - 5.00 - state tax piece
UT - Tax billed - UTAH - 432159 - 437854 - 5.00 - state piece

Total Tax billed Salt Lake City - state tax piece 15.00

UT - Taxable sales - salt lake city - 073714 - 238025 - 500.00
UT - Taxable sales - salt lake city - 651439 - 612345 - 500.00
UT - Taxable Sales - salt lake city - 432159 - 437854 - 500.00

Total Taxable Sales Salt Lake City 1500.00

UT - Taxable sales - UTAH salt lake state piece - 073714 - 238025 - 500.00
UT - Taxable sales - UTAH salt lake state piece - 651439 - 612345 - 500.00
UT - Taxable Sales - UTAH salt lake state piece - 432159 - 437854 - 500.00

The problem is the "UTAH" state piece if you sort by city/county it will
group with all other invoices and be unidentifiable to which city or county
the "state" piece belongs to.

Does this help?

Thank you so much for your help.

"Barb Reinhardt" wrote:

Could you give us more information on what you want to see and the results
you expect (with an example). I started looking at it and realized there
were some missing pieces.

"taxmom" wrote:

Hi, In the past you all have help me tremendiously.

I'm stuck again. I have a very unuserfriendly report that looks like this

State - ype - County/City - Invoice # - Ref # - Amount
UT - Tax Billed - Salt Lake - 073714 - 238025 - $10.82
UT - Taxable Sales - Utah - 073714 - 238025 - $983.75
UT - Tax Billed - Slt Lk Cnty 073714 - 238025 - 4.92
UT- taxable Sales Slt Lk Cnty 073714 - 238025 - 983.75
UT - Tax billed - Utah - 073714 - 238025 - 46.73

For sales tax reporting we need to know the state, city, county district
amounts. This would be simple if we could just sort by county/city, however
we cannot because the state tax piece only only shows the word "UTAH" it does
not give a city or county name. So we must sort by invoice #. This would be
ok if we only had a few invoices in each city such as Salt Lake but we have
have 40 or 50 invoice numbers in salt lake.

I need to find a way to keep all of the tax pieces that belong together, and
subtotal by city/county. I thought if I had a formula that could change the
state name "UTAH" to reflect the city for that invoice. This is the case for
all states. Each state is 10,000 or more lines. I need to somehow grouping
the information I need. I used the pivot table approach. However, since I
must sort by invoice I still have 10,000 lines. I want to sort by invoice to
get all the pieces then sort by county city. Any ideas of how I can do this?
something in Access perhaps? a formual in excel perhaps?

If you could please help with my dilema I would be eternally greatful.