(I will try not to bore anyone good\kind enough to spend their time reading
this, so I'll
cut out unneccessary details)

I'm a computer science student with plenty of experience with various
languages.
Unfortunately, I am not experienced with excel macros and excel tools and I
need to solve
a problem for a tracking spreadsheet for my part time job. It is as follows
(and any help would be MUCH appreciated) :

There are 3 worksheets in the workbook.
We'll call them Sheet 1, Sheet 2, Sheet 3 for simplicity.

It is for tracking sales on various dates for various categories of sales.
Let's just say a sale is of Type1, Type2, Type3 ... (up to say, 5).
These are tracked in columns with "check marks" (a number representing the
sales maker number)

The sale date (MM/DD/YY) is tracked in another column next to the Type
columns.
All of this is on Sheet1.

So it all looks like this:

Type 1 | Type 2 | Type 3 | Type 4 | Date (MM/DD/YY |
2 | | | | 08/01/03
|
| | 4 | | 08/02/03
|
| 1 | | | 08/03/03
|
3 | | | | 08/04/03
|
etc....

With all this in mind this is where the problem arises.
On sheet 3 I need to break down the sales into weekly sales (so between 2
given dates)
(which are dates in cells on Sheet1 because a week isn't always 7 days
unfortunately)
based on the type of sale.
EG (using the above example)
Week 1 had 2 Type1 sales in total.

I can do this on a line by line basis already by AND-ing the date with
whether it's of a certain Type, but it's for 30 to 400 sales per week, so it
needs to be some sort of loop (probably).

I know it's going to take some sort of macro or VB code, but I've never done
this before. (I am however a fast learner so don't avoid replying figuring
it will be wasted or confuse me, I just don't know how to approach it)

Also, feel free to reply to me at

Type 1 | Type 2 | Type 3 | Type 4 | Date (MM/DD/YY |
2 | | | | 08/01/03
|
| | 4 | | 08/02/03
|
| 1 | | | 08/03/03
|
3 | | | | 08/04/03
|

Hope this formats properly, the one in the original post did not obviously
=p

Hey thanks, very nice solution.
I adapted it to the specific spreadsheet and it does work
except using a sum make the total sales of that type inaccurate because the
numbers in the "Type" column represent the sales maker number not the number
of sales
because each entry (row) is 1 sale, with the sales maker number representing
who sold it.

(EG: if column "Type 1" consists of vertical entries 1, 4, 2, 6
(representing, say, myself, and 3 other sales people, but only 4 sales) it
comes back as 13 not 4)

I used a CountIF 0 to get a total for all sales makers instead of Sum for
a different problem in my spreadsheet. Is there any way we could use
something like that here?

Still though, very very helpful, and my hopes are up here =) thanks so far.

"Debra Dalgleish" wrote in message
...
With your start date in cell I1, and end date in K2, you could use the
following formula to calculate the total for column A:

=SUMIF($E$2:$E$5,"="&$I$1,A2:A5)-SUMIF($E$2:$E$5,""&$K$1,A2:A5)

Copy the formula across to calculate the remaining columns.

Trevor Stokes wrote:
(I will try not to bore anyone good\kind enough to spend their time
reading
this, so I'll
cut out unneccessary details)

I'm a computer science student with plenty of experience with various
languages.
Unfortunately, I am not experienced with excel macros and excel tools
and I
need to solve
a problem for a tracking spreadsheet for my part time job. It is as
follows
(and any help would be MUCH appreciated) :

There are 3 worksheets in the workbook.
We'll call them Sheet 1, Sheet 2, Sheet 3 for simplicity.

It is for tracking sales on various dates for various categories of
sales.
Let's just say a sale is of Type1, Type2, Type3 ... (up to say, 5).
These are tracked in columns with "check marks" (a number representing
the
sales maker number)

The sale date (MM/DD/YY) is tracked in another column next to the Type
columns.
All of this is on Sheet1.

So it all looks like this:

Type 1 | Type 2 | Type 3 | Type 4 | Date (MM/DD/YY |
2 | | | | 08/01/03
|
| | 4 | | 08/02/03
|
| 1 | | | 08/03/03
|
3 | | | | 08/04/03
|
etc....

With all this in mind this is where the problem arises.
On sheet 3 I need to break down the sales into weekly sales (so between
2
given dates)
(which are dates in cells on Sheet1 because a week isn't always 7 days
unfortunately)
based on the type of sale.
EG (using the above example)
Week 1 had 2 Type1 sales in total.

I can do this on a line by line basis already by AND-ing the date with
whether it's of a certain Type, but it's for 30 to 400 sales per week,
so it
needs to be some sort of loop (probably).

I know it's going to take some sort of macro or VB code, but I've never
done
this before. (I am however a fast learner so don't avoid replying
figuring
it will be wasted or confuse me, I just don't know how to approach it)




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Read my previous reply to this post first, then this one:

If I were to use your formula, but, needing the count, not the sum, can you
think of a way to divide each cell by itself (to obtain 1) in the same
formula? I tried:

(($E$2)/($E$2):($E$5)/($E$5)) for the range (trying to divide each cell
value by itself.

Obviously this returns a formula error, but is there a way to do this
approach?
If so, this will work fully.

Otherwise ignore this post as it is then useless.


"Debra Dalgleish" wrote in message
...
With your start date in cell I1, and end date in K2, you could use the
following formula to calculate the total for column A:

=SUMIF($E$2:$E$5,"="&$I$1,A2:A5)-SUMIF($E$2:$E$5,""&$K$1,A2:A5)

Copy the formula across to calculate the remaining columns.

Trevor Stokes wrote:
(I will try not to bore anyone good\kind enough to spend their time
reading
this, so I'll
cut out unneccessary details)

I'm a computer science student with plenty of experience with various
languages.
Unfortunately, I am not experienced with excel macros and excel tools
and I
need to solve
a problem for a tracking spreadsheet for my part time job. It is as
follows
(and any help would be MUCH appreciated) :

There are 3 worksheets in the workbook.
We'll call them Sheet 1, Sheet 2, Sheet 3 for simplicity.

It is for tracking sales on various dates for various categories of
sales.
Let's just say a sale is of Type1, Type2, Type3 ... (up to say, 5).
These are tracked in columns with "check marks" (a number representing
the
sales maker number)

The sale date (MM/DD/YY) is tracked in another column next to the Type
columns.
All of this is on Sheet1.

So it all looks like this:

Type 1 | Type 2 | Type 3 | Type 4 | Date (MM/DD/YY |
2 | | | | 08/01/03

|
| | 4 | | 08/02/03
|
| 1 | | | 08/03/03
|
3 | | | | 08/04/03
|
etc....

With all this in mind this is where the problem arises.
On sheet 3 I need to break down the sales into weekly sales (so between
2
given dates)
(which are dates in cells on Sheet1 because a week isn't always 7 days
unfortunately)
based on the type of sale.
EG (using the above example)
Week 1 had 2 Type1 sales in total.

I can do this on a line by line basis already by AND-ing the date with
whether it's of a certain Type, but it's for 30 to 400 sales per week,
so it
needs to be some sort of loop (probably).

I know it's going to take some sort of macro or VB code, but I've never
done
this before. (I am however a fast learner so don't avoid replying
figuring
it will be wasted or confuse me, I just don't know how to approach it)




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

To count the entries, you could use the Sumproduct function:

=SUMPRODUCT(($E$2:$E$5=$I$1)*($E$2:$E$5<=$K$1)*(A 2:A5<""))

Trevor Stokes wrote:
Read my previous reply to this post first, then this one:

If I were to use your formula, but, needing the count, not the sum, can you
think of a way to divide each cell by itself (to obtain 1) in the same
formula? I tried:

(($E$2)/($E$2):($E$5)/($E$5)) for the range (trying to divide each cell
value by itself.

Obviously this returns a formula error, but is there a way to do this
approach?
If so, this will work fully.

Otherwise ignore this post as it is then useless.


"Debra Dalgleish" wrote in message
...

With your start date in cell I1, and end date in K2, you could use the
following formula to calculate the total for column A:

=SUMIF($E$2:$E$5,"="&$I$1,A2:A5)-SUMIF($E$2:$E$5,""&$K$1,A2:A5)

Copy the formula across to calculate the remaining columns.

Trevor Stokes wrote:

(I will try not to bore anyone good\kind enough to spend their time

reading

this, so I'll
cut out unneccessary details)

I'm a computer science student with plenty of experience with various
languages.
Unfortunately, I am not experienced with excel macros and excel tools

and I

need to solve
a problem for a tracking spreadsheet for my part time job. It is as

follows

(and any help would be MUCH appreciated) :

There are 3 worksheets in the workbook.
We'll call them Sheet 1, Sheet 2, Sheet 3 for simplicity.

It is for tracking sales on various dates for various categories of

sales.

Let's just say a sale is of Type1, Type2, Type3 ... (up to say, 5).
These are tracked in columns with "check marks" (a number representing

the

sales maker number)

The sale date (MM/DD/YY) is tracked in another column next to the Type
columns.
All of this is on Sheet1.

So it all looks like this:

Type 1 | Type 2 | Type 3 | Type 4 | Date (MM/DD/YY |
2 | | | | 08/01/03


|
| | 4 | | 08/02/03
|
| 1 | | | 08/03/03
|
3 | | | | 08/04/03
|
etc....

With all this in mind this is where the problem arises.
On sheet 3 I need to break down the sales into weekly sales (so between

2

given dates)
(which are dates in cells on Sheet1 because a week isn't always 7 days
unfortunately)
based on the type of sale.
EG (using the above example)
Week 1 had 2 Type1 sales in total.

I can do this on a line by line basis already by AND-ing the date with
whether it's of a certain Type, but it's for 30 to 400 sales per week,

so it

needs to be some sort of loop (probably).

I know it's going to take some sort of macro or VB code, but I've never

done

this before. (I am however a fast learner so don't avoid replying

figuring

it will be wasted or confuse me, I just don't know how to approach it)




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html