ExcelBanter - Sum customers by date

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sum customers by date (https://www.excelbanter.com/excel-discussion-misc-queries/129167-sum-customers-date.html)

Secret Squirrel

Sum customers by date

I'm trying to sum invoice amounts for a specific customer by date. Along with
that I also want to group them by class. I have 3 classes, 1, 2, and 3.
Here's a little snap shot of my worksheet

Column A Column B Column C Column D
(Inv Date) (Customer) (Inv Amt) (Class)

2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 2
2/2/2007 MyCompany $100.00 1
2/2/2007 MyCompany $100.00 3

So on my summary worksheet I have the following:

2/1/2007 $200.00 for class 1
2/1/2007 $100.00 for class 2
2/2/2007 $100.00 for class 1
2/2/2007 $100.00 for class 3

I have multiple customers so I only want to single out one company. I'm
using "MyCompany" as the example name of the company.

Any help would be greatly appreciated.

Bernard Liengme

Sum customers by date

Put the date in J1, the company in K1, class in L1 and use
=SUMPRODUCT(--(A1:A200=J1),--(B1:B200=K1),--(D1:D200)=L1,C1:C100)
for more info see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

OR
make a Pivot Table; see
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/2...le-parameters/

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Secret Squirrel" wrote in
message ...
I'm trying to sum invoice amounts for a specific customer by date. Along
with
that I also want to group them by class. I have 3 classes, 1, 2, and 3.
Here's a little snap shot of my worksheet

Column A Column B Column C Column D
(Inv Date) (Customer) (Inv Amt) (Class)

2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 2
2/2/2007 MyCompany $100.00 1
2/2/2007 MyCompany $100.00 3

So on my summary worksheet I have the following:

2/1/2007 $200.00 for class 1
2/1/2007 $100.00 for class 2
2/2/2007 $100.00 for class 1
2/2/2007 $100.00 for class 3

I have multiple customers so I only want to single out one company. I'm
using "MyCompany" as the example name of the company.

Any help would be greatly appreciated.

Secret Squirrel

Sum customers by date

Thanks Bernard!

"Bernard Liengme" wrote:

Put the date in J1, the company in K1, class in L1 and use
=SUMPRODUCT(--(A1:A200=J1),--(B1:B200=K1),--(D1:D200)=L1,C1:C100)
for more info see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

OR
make a Pivot Table; see
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/2...le-parameters/

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Secret Squirrel" wrote in
message ...
I'm trying to sum invoice amounts for a specific customer by date. Along
with
that I also want to group them by class. I have 3 classes, 1, 2, and 3.
Here's a little snap shot of my worksheet

Column A Column B Column C Column D
(Inv Date) (Customer) (Inv Amt) (Class)

2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 2
2/2/2007 MyCompany $100.00 1
2/2/2007 MyCompany $100.00 3

So on my summary worksheet I have the following:

2/1/2007 $200.00 for class 1
2/1/2007 $100.00 for class 2
2/2/2007 $100.00 for class 1
2/2/2007 $100.00 for class 3

I have multiple customers so I only want to single out one company. I'm
using "MyCompany" as the example name of the company.

Any help would be greatly appreciated.

driller

Sum customers by date

Hi Secret Squirrel,

for summing and/or with grouping of your invoice records [by dates, by
class, by numbers, by whichever] in the spreadsheet, may i suggest that you
use excel's pivot table...
regards
--
*****
birds of the same feather flock together..

"Secret Squirrel" wrote:

I'm trying to sum invoice amounts for a specific customer by date. Along with
that I also want to group them by class. I have 3 classes, 1, 2, and 3.
Here's a little snap shot of my worksheet

Column A Column B Column C Column D
(Inv Date) (Customer) (Inv Amt) (Class)

2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 2
2/2/2007 MyCompany $100.00 1
2/2/2007 MyCompany $100.00 3

So on my summary worksheet I have the following:

2/1/2007 $200.00 for class 1
2/1/2007 $100.00 for class 2
2/2/2007 $100.00 for class 1
2/2/2007 $100.00 for class 3

I have multiple customers so I only want to single out one company. I'm
using "MyCompany" as the example name of the company.

Any help would be greatly appreciated.

Secret Squirrel

Sum customers by date

Secret Squirrel

Sum customers by date

Thanks driller I will take that into consideration. It might be a better way
to handle this worksheet since it's getting rather large. Appreciate your
input!

SS

"driller" wrote:

Hi Secret Squirrel,

for summing and/or with grouping of your invoice records [by dates, by
class, by numbers, by whichever] in the spreadsheet, may i suggest that you
use excel's pivot table...
regards
--
*****
birds of the same feather flock together..

"Secret Squirrel" wrote:

I'm trying to sum invoice amounts for a specific customer by date. Along with
that I also want to group them by class. I have 3 classes, 1, 2, and 3.
Here's a little snap shot of my worksheet

Column A Column B Column C Column D
(Inv Date) (Customer) (Inv Amt) (Class)

2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 1
2/1/2007 MyCompany $100.00 2
2/2/2007 MyCompany $100.00 1
2/2/2007 MyCompany $100.00 3

So on my summary worksheet I have the following:

2/1/2007 $200.00 for class 1
2/1/2007 $100.00 for class 2
2/2/2007 $100.00 for class 1
2/2/2007 $100.00 for class 3

I have multiple customers so I only want to single out one company. I'm
using "MyCompany" as the example name of the company.

Any help would be greatly appreciated.

All times are GMT +1. The time now is 04:26 PM.