Subtotal group without resorting question

[TiChNi]

I have four columns the first with customer, the next with state, the
next with salesman and the next with sales amount. The customer,
state and saleman will often repeat but not necessarily consecutively
(they are not sorted or grouped in any particular way).

I simply want to find total sales (1) by salesman, (2) by state and
(3) by customer.

I can do it manually by running multiple Sorts and creating a
different subtotal range after every sort, but would think there's an
easier way to do it.

Is there a lookup that is combined with a subtotal or some other
function to do this in one step instead of three or more?

Thanks for your help.

 

[Alexander Wolff]

Data Pivot-Table. Get into it. It is worth it, believe me!

 

[Pete_UK]

Let's say your data occupies A2100, with headers on row 1. List your
salesmen, let's say in F2:F15, and put this formula in G2:

=SUMIF(A$2:A$100,F2,D$2$100)

format as currency, and copy down to G15 - there is your table of
sales by salesman. If you don't know how much data you have, you can
use column references for the first and 3rd parameter, like so:

=SUMIF(A:A,F2,D)

So, to obtain a breakdown by state, list your states in, say, I2
downwards, and put a similar formula in J2:

=SUMIF(A:A,J2,D)

and copy down as required.

Do you need me to spell it out for the customer table?

Hope this helps.

Pete

 

[Pete_UK]

Sorry, it should have been:

=SUMIF(A:A,I2,D)

for the formula in J2.

Hope this helps.

Pete