Finding data by dates
 

I want to make a personel sales sheet that will allow me to track my
commissions. I make a list for a entire month and start each line with
the date. What I want to do is to be able to call up all the sales for
each day to track sales. What I am needing is to be able find all sales
stats by day. Can some one tell me the look up formula to be able to
isolate mutible sales in one day by the date. here is an example

10/1/05 John doe 1 0 0 1 2
10/1/05 Jane doe 1 1 0 2 0
10/2/05 Rob doe 1 2 2 0 1

the numbers are how many items are sold for each item. I want to be
able to add only the 10/1/05 sales for example. If you have know the
formula and have time to answer please let me know. you post it here or
email me at

Thank you in advance.

One way is to use SUMIF ..

Assuming source data below is in
Sheet1, cols A to G, data from row2 down

Date Name P1 P2 P3 P4 P5
01-10-05 John doe 1 0 0 1 2
01-10-05 Jane doe 1 1 0 2 0
02-10-05 Rob doe 1 2 2 0 1
etc

(P1, P2 are the product names)

In Sheet2
---
The set-up comprises the dates listed in col A, A2 down
with the product names listed in B1 across to F1

Date P1 P2 P3 P4 P5
01-10-05
02-10-05
etc

Put in B2: =SUMIF(Sheet1!$A:$A,$A2,Sheet1!C:C)
Copy B2 across to F2, fill down to populate the table
This would yield, for the sample source table:

Date P1 P2 P3 P4 P5
01-10-05 2 1 0 3 2
02-10-05 1 2 2 0 1
etc

---
Another way would be to use a pivot table on the source table

Place the cursor anywhere within the source table in Sheet1

Click Data Pivot Table Report
Click Next Next

In step 3 of the wiz:
Drag and drop "Date" within the ROW area
Drag and drop "P1" within the DATA area
(It'll appear as Sum of P1)
Drag and drop "P2" within the DATA area (below "Sum of P1")
Do likewise for "P3" to "P5"
Click Finish

The pivot table (PT) will be created in a new sheet to the left of Sheet1
Go to the PT sheet, and do a simple re-arrangement
Drag "Data" (which'll appear in B1) and drop it on C1

This should yield the desired results:

___Data
Date Sum of P1 Sum of P2 Sum of P3 Sum of P4 Sum of P5
01-10-05 2 1 0 3 2
02-10-05 1 2 2 0 1
Grand Total 3 3 2 3 3

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
wrote in message
oups.com...
I want to make a personel sales sheet that will allow me to track my
commissions. I make a list for a entire month and start each line with
the date. What I want to do is to be able to call up all the sales for
each day to track sales. What I am needing is to be able find all sales
stats by day. Can some one tell me the look up formula to be able to
isolate mutible sales in one day by the date. here is an example

10/1/05 John doe 1 0 0 1 2
10/1/05 Jane doe 1 1 0 2 0
10/2/05 Rob doe 1 2 2 0 1

the numbers are how many items are sold for each item. I want to be
able to add only the 10/1/05 sales for example. If you have know the
formula and have time to answer please let me know. you post it here or
email me at

Thank you in advance.

The pivot table did exactly what I wanted. Thank so much for taking the
time to answer this question. Just writing the answer so it could be
understood must have taken a lot of time. I understand you did put a
lot of time in this answer and I just want you to know my thank you is
based on this knowledge. You totaly gave me what I needed. Thank you so
much again.

You're welcome! Glad you found one of the 2 options suitable.
Thanks for the feedback which is appreciated !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
wrote in message
oups.com...
The pivot table did exactly what I wanted. Thank so much for taking the
time to answer this question. Just writing the answer so it could be
understood must have taken a lot of time. I understand you did put a
lot of time in this answer and I just want you to know my thank you is
based on this knowledge. You totaly gave me what I needed. Thank you so
much again.