SUMPRODUCT MISSING SOME ITEMS

[Iain Scott]

Hello,

I hope someone can be of help here ...

I'm using SUMPRODUCT in this manner

=SUMPRODUCT((DATA07!A2:A2560>=H2)*(DATA07!A2:A2560<=H3)*(DATA07!B2:B2560={"MONDAY"}))

You can see it looks for the number of occurences of contacts on each
day of the week in a given month. To see which days are our busiest.

H2=Start date
H3=End date
DATA07!A2:A2560=Long list of dates
DATA07!B2:B2560=List of days of week

The problem I'm having is that this is still missing days, for example
say MAY actually had 47 individual contacts on a WEDNESDAY the
equation only shows 31 or less.

Is thaere a way of 'tightening up' this usage of SUMPRODUCT to give
more accurate results?

I thought the data might actaully be 'dirty' with dodgy unseen spaces
and characters in the various columns but I could not see these. I
understand there is a macro you can get which 'cleans' excel 2003
spreadsheets. is this still available and where?

Thanking You for your help!

Iain

 

[vsoler]

Hello,

I hope someone can be of help here ...

I'm using SUMPRODUCT in this manner

=SUMPRODUCT((DATA07!A2:A2560>=H2)*(DATA07!A2:A2560<=H3)*(DATA07!B2:B2560={"­MONDAY"}))

You can see it looks for the number of occurences of contacts on each
day of the week in a given month. To see which days are our busiest.

H2=Start date
H3=End date
DATA07!A2:A2560=Long list of dates
DATA07!B2:B2560=List of days of week

The problem I'm having is that this is still missing days, for example
say MAY actually had 47 individual contacts on a WEDNESDAY the
equation only shows 31 or less.

Is thaere a way of 'tightening up' this usage of SUMPRODUCT to give
more accurate results?

I thought the data might actaully be 'dirty' with dodgy unseen spaces
and characters in the various columns but I could not see these. I
understand there is a macro you can get which 'cleans' excel 2003
spreadsheets. is this still available and where?

Thanking You for your help!

Iain

lain,

1/ Why do you put {"MONDAY"} in brackets? Using quotes should be more
than enough!

2/ You can test your data doing the following:

In C2 type in (DATA07!A2>=$H$2)*(DATA07!A2<=$H$3)*(DATA07!B2={"­
MONDAY"})
You will get either 1 for TRUE or 0 for FALSE.

Copy the formula down to C2560.

This way you will understand visually what lines present a problem,
giving you an unexpected result.

Hope it helps

Vicente Soler

 

[Don Guillett]

=SUMPRODUCT((DATA07!A2:A2560>=H2)*(DATA07!A2:A2560<=H3)*(DATA07!B2:B2560={"MONDAY"}))
try this idea =SUMPRODUCT((MONTH(E2:E7)=6)*(WEEKDAY(E2:E7)=2))
=SUMPRODUCT((month(DATA07!A2:A2560)=1)*(weekday(DATA07!A2:A2560)=4))
for Wednesdays in January.

 

[T. Valko]

How are the weekdays generated in column B? Are they formulas?

Since the date can also be used to determine the weekday follow Don's
suggestion and just reference the column with the dates.

I understand there is a macro you can get which 'cleans'
excel 2003 spreadsheets. is this still available and where?

It's called TrimAll and can be found here:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Biff

 

[Iain Scott]

Hi,

Firstly, thank you guys for replying to this query!

I tried this formula ...

=SUMPRODUCT((MONTH(DATA07!A2:A825)=3)*(WEEKDAY(DATA07!A2:A825)=2))

.... to search for the number of occurences of Monday referrals during
the month of May

The solution presented, however, throws up a #VALUE! error.
"A value used in this formula is of the wrong data type."

The DATA07!A2:A825 column is a single column of dates from the start
of 2007. Format is "DATE" 02/05/07 dd/mm/yy
I reversed the formula thus ...
=SUMPRODUCT((WEEKDAY(DATA07!A2:A825)=2)*(MONTH(DATA07!A2:A825)=3))
and also changed the format to "DATE" dd-mmm

I'm kind of lost from here and the real meaning of this error ...

Iain

 

[T. Valko]

If you're getting a #VALUE! error that means all or some of your dates
aren't really dates or, you have some text entries intermingled with you
dates or, you already have #VALUE! errors in the range.

Check and make sure your dates are true Excel dates. A true Excel date is
really just a number formatted to look like a date. For example, 6/2/2007
(June 6 2007) is a true Excel date and is *displayed* in a cell as 6/2/2007.
However, the true underlying value of that cell is the number 39235.

If your range of date cells is full (no empty/blank cells) then this
formula:

=COUNT(DATA07!A2:A825)

Should return 824. If it doesn't then some (or all) of your dates aren't
true Excel dates and are TEXT strings that look like dates.

One way that might convert TEXT dates to true Excel dates:

Select the range of cells in question
Goto the menu Data>Text to Columns
Click Next twice
In Step 3 of the wizard select Date and your choice of format
Click Finish

Biff

 

[Iain Scott]

Hi,

I found TrimAll and installed it, so thanks for that.

Does it make a difference with this VALUE error that the format of the
dates is sometimes ...

dd/mm/yyyy

dd/mm/yyyy h:m:s

.... would I be having to convert all the dates to dd/mm/yyyy?

Instead of value errors I'm now getting blank cells.

Obviously having the times of the busiest days is of value to us as
well and I'd be reluctant to lose these. Is there an excel way of
separating the dates and the times into two separate columns for
separate analysis?

Thank you in advance anybody for any help offered here.

Iain Scott

 

[T. Valko]

The format doesn't matter *as long as the dates/times are true Excel
dates/times*.

If you're getting #VALUE! that *usually* means some or all of your
dates/times are not true Excel dates/times. True Excel dates/times are
really just numbers formatted to look like a date/time. For example:

6/5/2007 (m/d/y) has the numeric value of 39238 but it's *displayed* as the
date 6/5/2007

12:00 PM has the numeric value of 0.5 but it's *displayed* as the time 12:00
PM.

6/5/2007 12:00 PM has the numeric value of 39238.5 but it's *displayed* as
6/5/2007 12:00 PM

You may have *TEXT* strings that look like dates. For example:

6/5/2007 *looks* like a date but it may actually be just a TEXT string. This
happens when the cell has been formatted as TEXT or sometimes happens when
data is imported/copy/pasted from another application.

You can check and make sure the dates/times are true Excel dates/times using
this formula:

=ISNUMBER(A1)

If the date/time is a true Excel date/time that formula will return TRUE. If
it returns FALSE the date/time is a TEXT string.

Biff

 

[Iain Scott]

Hi,

I just want to say that I tried the recommendations made here for the
solution of this problem and they worked!

The data is now coming back to me a great deal more accurately than
before.

I'm going to look at other aspects of the spresdsheet to see if the
same principle can be applied to timing data.

Thank you again for the help and advice freely offered. I envy your
broad knowledge of the (to me) more arcane and seemingly
solution-unpenetrable aspects of an, albeit otherwise excellent,
program like Excel!

Iain

 

[Don Guillett]

For archival purposes it is always nice to post your final solution.

 

[Iain Scott]

Hi,

Of course.

I went with this solution in the end ...

=SUMPRODUCT((month(DATA07!A2:A2560)=1)*(weekday(DATA07!A2:A2560)=4))

.... which worked perfectly.

Can I ask though how one would put TIME in as well as hours in the
above type of statement?

I'm looking at the times of contacts with the days being divided into
1 hour blocks so I'd need to know the number of clients who came in
between the hours of ...

00:00 through 00:59
01:00 through 01:59
02:00 through 02:59

.... and so on for the entire 24hr day.

I need to input the month and the total counts per month of all times
contacts occured tallied in a one month summary of each individual
hour for 24 hours.

Thanks again!

Iain Scott

 

[Roger Govier]

Hi Iain

You could add the HOUR() function into your Sumproduct.
=HOUR(A1) where A1 held 11/Jun/2007 8:09 would return 8

so
=SUMPRODUCT((month(DATA07!A2:A2560)=1)*
(weekday(DATA07!A2:A2560)=4)*(HOUR(DATA07!A2:A2560)=8))

to pick up all entries between 8:00 and 8:59 on a Wednesday in January