Billing Statement Help

J

jeb

Hi, I am hoping that someone with a little more excel experience can help me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of time
filtering card users, running a sum for transactions, & copying & pasting to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?
 
D

Don Guillett

Why do that? Try this idea where a2 contains the name of the cardholder

=sumproduct((sourcesheet!a2:a22=a2)*(sourcesheet!b2:b22="deptname")*sourcesheet!c2:c22)
 
B

Bernard Liengme

Lets say the first row with Joe Brown in in A2 and there are 300 entries

Put a list of names in column G, starting in G2
Put a list of Departments in H1 to Z1 (whatever)
In H2 use =SUMPRODUCT(--($A$2:$A$350=$G2), --($B$2:$B$350=H$1), $C$2:$C$350)
Copy across and down as needed
I have used 350 to allow for extras.
Do NOT use full column reference (A:A) unless you have Excel 2007

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another (sometimes better) way is with a Pivot Table: see one or more of
these
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
 
R

Roger Govier

Hi

Use a Pivot Table.
Place your cursor within your source table>Data>Pivot Table >Finish
On the new sheet where the Pivot Table skeleton appears
Drag Cardholder to Row area
Drag Department to Row area
Drag Amount to Data Area
 
J

jeb

I've never used a pivot chart for Excel. I have used them in Access though.
The only hang-up with this pivot chart is that it gives me the total NUMBER
of transactions, not the TOTAL SPEND AMOUNT (JOE BROWN LOGISTICS
$12,000<total of 8 thransactions>). Any further advice?
 
B

Bernard Liengme

You can change this to give Count, Sum, Average, etc
See one of the links in my first message
 
R

Roger Govier

Hi

Double click on Count of Amount and change the function from Count to Sum.
There must be some blank values in your range - Amount.
If all values are numeric, Excel will use Sum, but if any are Text or Blank,
then it will default to Count.
 
Top