| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 __________________________ |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Column reference and column count | Excel Discussion (Misc queries) | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
| COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
| I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions | |||
Linear Mode
