Billing Statement Help

  • Thread starter Thread starter jeb
  • Start date Start date
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?
 
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)
 
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/
 
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
 
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?
 
You can change this to give Count, Sum, Average, etc
See one of the links in my first message
 
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.
 
Back
Top