formula advice

  • Thread starter Thread starter Sundew Chocobo
  • Start date Start date
S

Sundew Chocobo

Hi,

I need to figure out a formula that well count the number of scanners sent
out per week. Sounds simple, right? The days are listed using the DATE
function. The scanner information is in the column next to the weekdays.


ie


Date Scanner Serial #
1/1/2003 4963102150900054
2/10/2003 4963102150900934




2/18/2003 ?
4963102150900284


2/25/2003 4963102150901707
2/25/2003 4963102150901033
2/24/2003 4963102150901801
2/25/2003 4963102150901308
2/26/2003
3/3/2003
3/4/2003
3/17/2003 4963102150901396
3/18/2003 4963102150900391
3/20/2003 4963102150901034



I really could use some suggestions. (should I use the WEEKDAY function
with the COUNT function?)


TIA
 
You can use a Pivot table...

Highlight your table, drop the date in your Row fields and the serial # in
the data fields. right-click on the date, order - group and group by week.
Then, right-click on your data and make sure it's set to count.
 
not sure if reply posted...

??? The table has many columns (a,b,c,d,e,f,tg,h,i)

are you saying I should completely revamp the current table???
 
If you are looking to get scanners per week, Are you looking to this this
within this same table? In that case, your column would either have the same
value through the entire week or have many blank rows in between week start
and end.

My suggestion below would be a separat table to only count # of scanners per
week. No other data would be included.
 
thank you I'll try it.

Sean Timmons said:
If you are looking to get scanners per week, Are you looking to this this
within this same table? In that case, your column would either have the same
value through the entire week or have many blank rows in between week start
and end.

My suggestion below would be a separat table to only count # of scanners per
week. No other data would be included.
Click to expand...
 
didn't work...

again this is what I'm working with

Date Scanner Serial # Replacement Scanner Cable Sent Modem Serial # Replaced
Modem Power cord Modem Series Customer Name CUST # What's Done
10/2/2008 496310 Cenex 222222 replacement scanner
10/2/2008 SA 111111 replacement scanner
10/2/2008 496310 MOBIL MART 999999 replacement scanner


there are blank lines in Scanner# and Date are shown as daily. won't group
by week or count # by week. (I'm trying to get # sent (Sacnner#) by week.
This report will go way back.

Ideas? :)
 
Back
Top