One more thing to worry about if you use something like this (38930) to
represent a date. You may not get the same date if you have
tools|options|calculation tab|1904 date system checked.

But the other dates could be off, too. (I just find that (38930) hard to
use/understand.)

Ted McCastlain wrote:

Sweet the first formula worked like a champ.

It has been a long week. I probably did put the formula in the same
range. Jeesh!

You would not believe the way the workbook was initially set up; it
stretches sideways for miles with archane references to date. Rather
than have to re-do it entirely, I am going to take some of the serial
date values that are already there and use those.

When I have more time, I am going to re-do this; for that I am saving
your formulas.

Thanks for all of the help!

Dave Peterson wrote:
Removing the 06 from the formula didn't affect the circular reference. I'm
betting that you put the formula in a cell in one of those ranges that were used
within the formula.

Maybe:

=SUMPRODUCT(($G$4:$G$5000="Consulting")
*($T$4:$T$5000=38930)*($S$4:$S$5000<=38960)
*($D$4:$D$5000="Proposal"))

Watch your ranges (I read your initial post and your ranges weren't the same
size).

But if I inherited this from you, I would find it difficult to figure out.

=SUMPRODUCT(($G$4:$G$5000="Consulting")
*($T$4:$T$5000=date(2006,8,1))*($S$4:$S$5000<=dat e(2006,8,31))
*($D$4:$D$5000="Proposal"))

would be easier for me to understand.

But as long as I'm looking at a complete month, I'd use a variation of
DaddyLongLeg's:

=SUMPRODUCT(--($G$4:$G$5000="Consulting"),
--(TEXT($S$4:$S$5000,"yyyymm")="200608"),
--($D$4:$D$5000="Proposal"))

That seems the easiest for me to understand.


Ted McCastlain wrote:

Update:

I got rid of the circular reference by removing "06" from the array and
from the cell. The problem is that I will be referencing against the
serial dates.

Is there a way to incorporate the date checking using the serial dates
of a month? Instead of using the (TEXT(..."mmm yy"..) is possible to
use something like ($T$4:$T$5000=38930)*($S$4:$S$5000<=38960) I am
just not sure how to incorportate it into the formula from earlier...

=SUMPRODUCT(--($G$4:$G$5000="Consulting"),--(TEXT($S$4:$S$5000,"mmm
yy")="Sep"),--($D$4:$D$5000="Proposal"))
Ted McCastlain wrote:
Thanks for the help but two things:

When using the formula, Excel keeps say it is a circular reference.

Secondly, I inherited this workbook and do not have the time to change
it to a pivot table.

T


Dave Peterson wrote:
An unfortunate line break:

=SUMPRODUCT(--($G$4:$G$5000="Consulting"),
--(TEXT($S$4:$S$5000,"mmm yy")="Sep 06"),
--($D$4:$D$5000="Proposal"))



daddylonglegs wrote:

Hi Ted

for this sort of counting with multiple conditions SUMPRODUCT is probably
the best option. Assuming your dates are in column S try this

=SUMPRODUCT(--($G$4:$G$5000="Consulting"),--(TEXT($S$4:$S$5000,"mmm
yy")="Sep 06"),--($D$4:$D$5000="Proposal"))

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson