SUMPRODUCT Formula quirky....

[j razz]

=SUMPRODUCT(--(Tabulation!$A$3:$A$54>=$C$1),--(Tabulation!$B$3:$B$54<=
$E$1),--(Tabulation!AV$3:AV$54))

Givens:
$A$3:$A$54 = a range of dates starting with each sunday of each week.
$B$3:$B$54 = a range of dates ending on each saturday of each week.
AV$3:AV$54 = a range of data that I am wanting the formula to look at
and post the results from.

I am having a problem with this formula. If the date manually entered
in cell $C$1 is say 3/19/2007 and the date manually entered in cell $E
$1 is say 3/24/2007 my problem is as follows: If I have an entry that
begins on 3/19/2007 it is not counted unless I change the date to the
day before. Another issue is that the ending date is not capping off
the returned data from the range AV$3:AV$54. It is as if the <=$E$1
does not exist.

Any ideas on how to fix these issues?

Thanks,

j razz

 

[Peo Sjoblom]

Maybe the dates are text instead?
You can check it with

=ISNUMBER(Tabulation!A3)

should return TRUE if it is a date

do the same for the dates in B

Or check the cells where you put the dates (C1 and E1)
and make sure they are numbers

Other things that can throw off date formulas are if the dates have times as
well

For instance

03/19/2007 10:00 AM where the condition is <= 03/19/2007 will return FALSE
since 03/19/2007 is the same as 03/19/2007 00:00 AM thus it is less than
03/19/2007 10:00 AM


Regards,

Peo Sjoblom

 

[j razz]

All of them are showing up as dates as formatting, and all of them
produce TRUE when using the formula you gave: =ISNUMBER(Tabulation!
A3)

As for time, there is no time that enters the equation for this
portion of the spreadsheet.

Here is a copy of the spreadsheet for your viewing if you have any
other thoughts on how to fix this:
http://www.jrazzcreations.com/Spreadsheets/Coverkids_Log_w_Tabulations_w_Totals1.xls

Thanks for your help and looking into this for me.

j razz

 

[Bob Phillips]

First one

=SUMPRODUCT(--(Tabulation!$A$3:$A$54>=$C$1-WEEKDAY($C$1)+1),--(Tabulation!$B$3:$B$54<=
$E$1),--(Tabulation!AV$3:AV$54))


Don't understand what you mean by the second.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[j razz]

The second part was just saying that there are no time issues that
would interfere with the formula.


Thanks for the formula as it is working thus far. Would you mind
taking a little time and explaining what you did in the formula so
maybe I can learn how it works so nextime I might be able to help
someone in return? Thanks Bob.

j razz

 

[Bob Phillips]

All I did was adjust whatever date that was in C1 to the Sunday of that
week, so it would match up with the data in Tabulation.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)