Help with an Array

[Ted McCastlain]

Ok, here is what I need to do: I need to count the number of
"Consulting" column, the number of times it was initiated by
"Proposal", and the month that it occurred in, which is the
numerical (serial) value of the month. The month I am currently
working on is September. So if there were two proposals for consulting
services during the month, I need that to be reported back to a cell.

I started with the below array but just could not get it to work. It
is a variation of another array I used to check revenue using the same
parameters but without trying to separate the RFP and Consulting from
the list. I am pretty sure I am screwing up on the COUNTIF because I
get "0" in my answer cell.

=SUM(COUNTIF($G$4:$G$5000,"Consulting")*($T$4:$T$5000>=38930)*($S$4:$S$5000<=38960)*((COUNTIF(D4400,"Proposal"))))

RFP WEB 38935
PROPOSAL CONSULTING 38936
RFP MARKETING 38937
RFP CONSULTING 38938
PROPOSAL MARKETING 38939
PROPOSAL WEB 38940

Thanks for the help!

 

[Carim]

Hi Ted,

What about a pivot table ... which would also give you the flexibility
to move around your data ...

HTH
Cheers
Carim

 

[Guest]

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]

An unfortunate line break:

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

 

[Ted McCastlain]

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

 

[Ted McCastlain]

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"))

 

[Dave Peterson]

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<=date(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]

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]

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.)

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!