Any experts on the "if statement" desperate insight is needed!!!!!! IF,Count, DCOUNT AHHHGGGGGGG!

[Chris]

Good Evening,

I am having difficulty writing an If statement on my spreadsheet. What
I did was I have 2 spreadsheets that the first one has a master lister
of data. It is sorted by the following:
Sheet #1 has the following
Date, File Number, "Orgin", Name, Number of QTY

What I am trying to do is to create I think If statements and counts to
pull this data from date ranges and list is out on a second excel
spreadsheet. Idealy this is what I want to accomplish.

Sheet #2

Week Ending
October 8th
Orgin and date are the key becasue I want it sorted by this.

A count of the files in the date range (Say Oct 1st - Oct 8th) filter
out duplicates) then the same for say Oct 9th-Oct 16th, Oct 17th-23rd
and so on. 2 packets of results for each orgin A & B.

Same for a count of the names in that period of time. Now I don't need
to eliminate duplicates for the names becuase some files have multiple
names

An add of the number of qty.

So I am trying to get my output to look like this.

Week ending
October 8th
Orgin:A (its either going to be orgin A or B
Number of files: 10 count without duplicates)
Number of names: 15 (count with duplicates)
Number of QTY 123 (sum)

October 8th

Orgin B
Number of files: 12
number of names 20
number of QTY 150

October 15 (same format as the 8th and so on)

Orgin A:

If anyone knows how I can even get started on this, I would be very
greatful. I have read alot on the IF statement, Count, DCOUNT and I am
getting lost!!!!!

 

[Jay]

Hi Chris -

I have a feeling you would be better off using a Pivot Table if I understand
your application correctly. If you add a "WeekEnding" column to your data
list, I believe the PivotTable will take it from there and pump out your
summaries in a single table.

I'd need to see some of your data to be sure. If you'd like me to pursue
this, send me a sample of your data at (e-mail address removed). Or,
investigate pivottables more.

 

[Chris]

Hi Jay,

This is what I have. The first part is the raw data and the second part
is what I want to accomplish, sorting by 1 week intervals of time, then
by Orgin A / B, then by Potential date / Arrival Date. Counting the
files (elimating the duplicates) counting the names because each file
can have duplicate names, then adding the units to a final report
listed below.


Potential Date Arrival Date FileNumber Orgin Name Number ofUnits
10/28/06 11/24/06 1842
A Smith 1
10/28/06 11/24/06 1843
A Jones 1
10/28/06 11/24/06 1844
A Johnson 1
11/04/06 11/24/06 1884
A Oneil 1

Week Ending October 8th
October 15th
A B
A B

Potential Arrival Potential Arrival Potential Arrival Potential Arrival
Processed Files 40 0 7 0 72 0 12 0
Customers 56 0 7 0 76 0 12 0
Units 61 0 8 0 64 0 12 0


This is what I want to accomplish. Kind of a tally sheet first by
period of 1 week periods of time, then by A & B, then by Potential date
and Arrival date.

 

[Jay]

Chris -

Got it. I have to step away for about an hour. Will pick this back up in
one hour. Sorry for the delay.

 

[Jay]

Hi Chris -

1. Is the WeekEnding supposed to be based on the PotentialDate or the
ArrivalDate ?

2. What day of the week defines your week ending dates (Sat, Sun, Fri, etc.)?

 

[Chris]

Hi Jay,

Both, I want to have 2 sets of #s, one based on Potential date and the
other results on Arrival ending. Results for each date. For example
listed below would be for potential and then next to it the data would
be for arrival date.


Orgin:A (its either going to be orgin A or B

Week ending October 8th

Potential Arrival

 

[Jay]

Hi Chris -

This is a tad complex. I'll contact you via email where we can exchange
more information easier. We'll post the solution to this thread when we
develop one.

 

[Chris]

Cool Thanks Jay. I think I am going to try a nested if statement but
not too sure