How to reference one column against another
 

Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

How to reference one column against another
 

Hi Ted

One way
For NOT completed Proposals
=SUMPRODUCT(--(B:B="PROPOSAL"),--(ISBLANK(E:E)))
For Completed proposals
=SUMPRODUCT(--(B:B="PROPOSAL"),--(NOT(ISBLANK(E:E))))

Change text to "RFP" to deal with your not completed and not completed
RFP's
--
Regards

Roger Govier


"Ted McCastlain" wrote in message
oups.com...
Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed
column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

How to reference one column against another
 

Re Roger's reply:

With SUMPRODUCT you cannot specify complete columns so use B1:B1000 (for
example)

=SUMPRODUCT(--(B1:B1000="PROPOSAL"),--(ISBLANK(E1:E1000)))


"Roger Govier" wrote:

Hi Ted

One way
For NOT completed Proposals
=SUMPRODUCT(--(B:B="PROPOSAL"),--(ISBLANK(E:E)))
For Completed proposals
=SUMPRODUCT(--(B:B="PROPOSAL"),--(NOT(ISBLANK(E:E))))

Change text to "RFP" to deal with your not completed and not completed
RFP's
--
Regards

Roger Govier


"Ted McCastlain" wrote in message
oups.com...
Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed
column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

How to reference one column against another
 

On 31 Aug 2006 09:23:40 -0700, "Ted McCastlain"
wrote:

Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

Unless I'm missing something obvious the following will tell you the
number of completed Proposals. Alter as necessary for the number of
RFPs and change the last term to ="" to indicate the number of pending
items.

=SUMPRODUCT((B3:B100="PROPOSAL")*(A3:A100="P"))


Just an observation, but isn't column A redundant since you only have
a "P" when there is an entry in the Date completed.

In which case change the formula to

=SUMPRODUCT((B3:B100="PROPOSAL")*(E3:E100=""))

and get rid of the data in column A.

Incidentally why were some of the date formats different. some were
d/m/y others are m/d/y ?

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

....perhaps US date format ???

"Richard Buttrey" wrote:

On 31 Aug 2006 09:23:40 -0700, "Ted McCastlain"
wrote:

Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

Unless I'm missing something obvious the following will tell you the
number of completed Proposals. Alter as necessary for the number of
RFPs and change the last term to ="" to indicate the number of pending
items.

=SUMPRODUCT((B3:B100="PROPOSAL")*(A3:A100="P"))


Just an observation, but isn't column A redundant since you only have
a "P" when there is an entry in the Date completed.

In which case change the formula to

=SUMPRODUCT((B3:B100="PROPOSAL")*(E3:E100=""))

and get rid of the data in column A.

Incidentally why were some of the date formats different. some were
d/m/y others are m/d/y ?

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

That's what they looked like, but why the mixture of styles in the
same table?

Rgds

On Thu, 31 Aug 2006 11:01:01 -0700, Toppers
wrote:

...perhaps US date format ???

"Richard Buttrey" wrote:

On 31 Aug 2006 09:23:40 -0700, "Ted McCastlain"
wrote:

Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

Unless I'm missing something obvious the following will tell you the
number of completed Proposals. Alter as necessary for the number of
RFPs and change the last term to ="" to indicate the number of pending
items.

=SUMPRODUCT((B3:B100="PROPOSAL")*(A3:A100="P"))


Just an observation, but isn't column A redundant since you only have
a "P" when there is an entry in the Date completed.

In which case change the formula to

=SUMPRODUCT((B3:B100="PROPOSAL")*(E3:E100=""))

and get rid of the data in column A.

Incidentally why were some of the date formats different. some were
d/m/y others are m/d/y ?

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

04/30/06 = 30th April

04/05/06 = 5th April

No mix of styles !

"Richard Buttrey" wrote:

That's what they looked like, but why the mixture of styles in the
same table?

Rgds

On Thu, 31 Aug 2006 11:01:01 -0700, Toppers
wrote:

...perhaps US date format ???

"Richard Buttrey" wrote:

On 31 Aug 2006 09:23:40 -0700, "Ted McCastlain"
wrote:

Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

Unless I'm missing something obvious the following will tell you the
number of completed Proposals. Alter as necessary for the number of
RFPs and change the last term to ="" to indicate the number of pending
items.

=SUMPRODUCT((B3:B100="PROPOSAL")*(A3:A100="P"))


Just an observation, but isn't column A redundant since you only have
a "P" when there is an entry in the Date completed.

In which case change the formula to

=SUMPRODUCT((B3:B100="PROPOSAL")*(E3:E100=""))

and get rid of the data in column A.

Incidentally why were some of the date formats different. some were
d/m/y others are m/d/y ?

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

No, all the dates are consistent with mm/dd/yy format - most are for
April, but some are for Feb, March and May.

Pete

Richard Buttrey wrote:
That's what they looked like, but why the mixture of styles in the
same table?

Rgds

On Thu, 31 Aug 2006 11:01:01 -0700, Toppers
wrote:

...perhaps US date format ???

"Richard Buttrey" wrote:

On 31 Aug 2006 09:23:40 -0700, "Ted McCastlain"
wrote:

Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!

Unless I'm missing something obvious the following will tell you the
number of completed Proposals. Alter as necessary for the number of
RFPs and change the last term to ="" to indicate the number of pending
items.

=SUMPRODUCT((B3:B100="PROPOSAL")*(A3:A100="P"))


Just an observation, but isn't column A redundant since you only have
a "P" when there is an entry in the Date completed.

In which case change the formula to

=SUMPRODUCT((B3:B100="PROPOSAL")*(E3:E100=""))

and get rid of the data in column A.

Incidentally why were some of the date formats different. some were
d/m/y others are m/d/y ?

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

On 31 Aug 2006 11:27:18 -0700, "Pete_UK" wrote:

No, all the dates are consistent with mm/dd/yy format - most are for
April, but some are for Feb, March and May.

Pete

Mea culpa

Quite correct now that I look again.
Put it down to a UK centric view of life where something like 04/28/06
looks odd to these old eyes.

:-(

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

Thanks all for the help. I am inheriting this workbook and
unfortunately do not have the time to redesign it as I would like.

As for why the dates were in different formats, I have no idea. You
should see the rest of the workbook!


Richard Buttrey wrote:
On 31 Aug 2006 11:27:18 -0700, "Pete_UK" wrote:

No, all the dates are consistent with mm/dd/yy format - most are for
April, but some are for Feb, March and May.

Pete

Mea culpa

Quite correct now that I look again.
Put it down to a UK centric view of life where something like 04/28/06
looks odd to these old eyes.

:-(

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to reference one column against another
 

Thanks Toppers.
Complete mental blockage today - put it down to anaesthetic still in
system from recent hospital visit!!!

--
Regards

Roger Govier


"Toppers" wrote in message
...
Re Roger's reply:

With SUMPRODUCT you cannot specify complete columns so use B1:B1000
(for
example)

=SUMPRODUCT(--(B1:B1000="PROPOSAL"),--(ISBLANK(E1:E1000)))


"Roger Govier" wrote:

Hi Ted

One way
For NOT completed Proposals
=SUMPRODUCT(--(B:B="PROPOSAL"),--(ISBLANK(E:E)))
For Completed proposals
=SUMPRODUCT(--(B:B="PROPOSAL"),--(NOT(ISBLANK(E:E))))

Change text to "RFP" to deal with your not completed and not
completed
RFP's
--
Regards

Roger Govier


"Ted McCastlain" wrote in message
oups.com...
Here is what I need to do:

I need to count the number of "Proposals" and the number of
"RFP" separately and those two values are contained within the same
column. Then I need cross reference that to the date completed
column.
Here is the problem I am running to. I can count the number of RFP
and Proposals using the COUNTIF function but I cannot figure out a
way
to determine which number of proposals and RFPs are complete or
pending. I am referencing the data to dates inputted into another
column.

The "P" represents a check mark showing the task complete.

P Type Start Date Due Date Completion Date


P PROPOSAL 02/22/06 04/01/06 04/30/06
P PROPOSAL 04/03/06 04/05/06 04/05/06
PROPOSAL 04/03/06 04/05/06
P RFP 03/30/06 04/07/06 04/04/06
P PROPOSAL 04/07/06 04/10/06 04/10/06
P RFP 04/07/06 04/11/06 04/11/06
P RFP 04/17/06 04/24/06 04/24/06
RFP 04/28/06 05/01/06
Thanks for the help!