How to reference one column against another

T

Ted McCastlain

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!
 
R

Roger Govier

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
 
G

Guest

Re Roger's reply:

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

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

Richard Buttrey

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!
Click to expand...

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
__________________________
 
G

Guest

....perhaps US date format ???

Richard Buttrey said:
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
__________________________
Click to expand...
 
R

Richard Buttrey

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

Rgds

...perhaps US date format ???
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
P

Pete_UK

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

Pete
 
R

Richard Buttrey

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

Pete
Click to expand...

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
__________________________
 
T

Ted McCastlain

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!
 
R

Roger Govier

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

time_report_date-wise_employee-wise 6
Sum amount of days in another coloumn 8
Count based on multiple criteria 2
Sum value between two dates and copy to new cell 6
Complete Tax Years 1
Arranging dates in a single column 5
Finding most recent date for each customer - XL 2007 2
Count months away betwwen a strat and end date by month 1

Top