Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
I have a resource planning sheet. In column A, I have a drop menu which
allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Here's an array formula that will work
=SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56*pm, Greg in CO <Greg in wrote: I have a resource planning sheet. *In column A, I have a drop menu which allows the user to select a job position which the think they may need. *In column S, they can put the estimate hour for that job. *Column S is for January. I would like to count the entires in column S which equate to an entry in column A. *I have tried countif, counta, nested if formulas...I am stumped. * The logic should be "if there are entries in column A, count the corresponding entries in column S. *Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). *So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Thanks JP...I tried that, but ti didn't work. I may not have been detailed
enough in my original description. Column A (Planned Resource) will have a job description (i.e. Lead Chicken Plucker) Column S will have an entry for hours (i.e. 40 hours for Jan) So, as I read across the worksheet, I see the the project requires 1 Planned Resource (Lead Chicken Plucker) for 40 hours in Jan. In a cell above January, I am trying to do a count (not a sum) of resources who have hours allocated in January. Ideally, a formula would look at Column A, confirm there is an entry (what the entry is is immaterial at this point, it is just confirming a "Planned Resource" versus an "Assigned Resource"), then look across to see if there is an entry in the same row in Column S. This would tell me that there is one Planned Resource needed in January. As for summing, I already have those formulas, using SUMIF functions. It's getting counts that is a pain. Column C has the same info, but is the listing of Assigned Resources and there is a cell above January for that count as well (it would be the same formula, just ref'ing to Column C instead of Column A). My head hurts again... ;) -- Greg "JP" wrote: Here's an array formula that will work =SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56 pm, Greg in CO <Greg in wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Why doesn't =COUNTIF(MyRange,TargetCell) work?
Perhaps you should explain what you mean by "didn't work". You should also consider posting a small sample of data, what specific formulas you used that didn't work, your expected outcome, Excel version, etc. It sounds like you are trying to use Excel to do Project Management. You should probably use MS Project for that, it's much better suited for the job of resource planning and scheduling. --JP On Aug 12, 4:10*pm, Greg in CO wrote: Thanks JP...I tried that, but ti didn't work. *I may not have been detailed enough in my original description. Column A (Planned Resource) will have a job description (i.e. Lead Chicken Plucker) Column S will have an entry for hours (i.e. 40 hours for Jan) So, as I read across the worksheet, I see the the project requires 1 Planned Resource (Lead Chicken Plucker) for 40 hours in Jan. *In a cell above January, I am trying to do a count (not a sum) of resources who have hours allocated in January. *Ideally, a formula would look at Column A, confirm there is an entry (what the entry is is immaterial at this point, it is just confirming a "Planned Resource" versus an "Assigned Resource"), then look across to see if there is an entry in the same row in Column S. *This would tell me that there is one Planned Resource needed in January. *As for summing, I already have those formulas, using SUMIF functions. *It's getting counts that is a pain. Column C has the same info, but is the listing of Assigned Resources and there is a cell above January for that count as well (it would be the same formula, just ref'ing to Column C instead of Column A). *My head hurts again... * ;) -- Greg "JP" wrote: Here's an array formula that will work =SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56 pm, Greg in CO <Greg in wrote: I have a resource planning sheet. *In column A, I have a drop menu which allows the user to select a job position which the think they may need. *In column S, they can put the estimate hour for that job. *Column S is for January. I would like to count the entires in column S which equate to an entry in column A. *I have tried countif, counta, nested if formulas...I am stumped. * The logic should be "if there are entries in column A, count the corresponding entries in column S. *Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). *So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Good idea!
Here is some sample data: Column A - Planned Position Column C - Assigned Position Column S - Jan Row 1 - Lead Chicken Plucker 40 Row 2 - Feather Sweeper 20 Row 3 - Beak Tweeker 10 Row 4------------------------------Assistant Beak Tweeker 30 So, this shows that: I have 3 planned Positions (no warm bodies to fill them yet) and 1 Assigned position (an actual person has been assigned). Hours estimated for these folks are listed in Columns S. I would like to show in two different cells above the January entry: How many Planned Positions have hours in January How many Assigned Positions have hours in January This would then be carried across the entire sheet, showing a count Planned and Assigned resources for each month. Ow! the pain in the head!......... ;) THis is part of a larger reosurce planning and allocation worksheet. -- Greg "JP" wrote: Why doesn't =COUNTIF(MyRange,TargetCell) work? Perhaps you should explain what you mean by "didn't work". You should also consider posting a small sample of data, what specific formulas you used that didn't work, your expected outcome, Excel version, etc. It sounds like you are trying to use Excel to do Project Management. You should probably use MS Project for that, it's much better suited for the job of resource planning and scheduling. --JP On Aug 12, 4:10 pm, Greg in CO wrote: Thanks JP...I tried that, but ti didn't work. I may not have been detailed enough in my original description. Column A (Planned Resource) will have a job description (i.e. Lead Chicken Plucker) Column S will have an entry for hours (i.e. 40 hours for Jan) So, as I read across the worksheet, I see the the project requires 1 Planned Resource (Lead Chicken Plucker) for 40 hours in Jan. In a cell above January, I am trying to do a count (not a sum) of resources who have hours allocated in January. Ideally, a formula would look at Column A, confirm there is an entry (what the entry is is immaterial at this point, it is just confirming a "Planned Resource" versus an "Assigned Resource"), then look across to see if there is an entry in the same row in Column S. This would tell me that there is one Planned Resource needed in January. As for summing, I already have those formulas, using SUMIF functions. It's getting counts that is a pain. Column C has the same info, but is the listing of Assigned Resources and there is a cell above January for that count as well (it would be the same formula, just ref'ing to Column C instead of Column A). My head hurts again... ;) -- Greg "JP" wrote: Here's an array formula that will work =SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56 pm, Greg in CO <Greg in wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
The hours should be under Column S......
-- Greg "Greg in CO" wrote: Good idea! Here is some sample data: Column A - Planned Position Column C - Assigned Position Column S - Jan Row 1 - Lead Chicken Plucker 40 Row 2 - Feather Sweeper 20 Row 3 - Beak Tweeker 10 Row 4------------------------------Assistant Beak Tweeker 30 So, this shows that: I have 3 planned Positions (no warm bodies to fill them yet) and 1 Assigned position (an actual person has been assigned). Hours estimated for these folks are listed in Columns S. I would like to show in two different cells above the January entry: How many Planned Positions have hours in January How many Assigned Positions have hours in January This would then be carried across the entire sheet, showing a count Planned and Assigned resources for each month. Ow! the pain in the head!......... ;) THis is part of a larger reosurce planning and allocation worksheet. -- Greg "JP" wrote: Why doesn't =COUNTIF(MyRange,TargetCell) work? Perhaps you should explain what you mean by "didn't work". You should also consider posting a small sample of data, what specific formulas you used that didn't work, your expected outcome, Excel version, etc. It sounds like you are trying to use Excel to do Project Management. You should probably use MS Project for that, it's much better suited for the job of resource planning and scheduling. --JP On Aug 12, 4:10 pm, Greg in CO wrote: Thanks JP...I tried that, but ti didn't work. I may not have been detailed enough in my original description. Column A (Planned Resource) will have a job description (i.e. Lead Chicken Plucker) Column S will have an entry for hours (i.e. 40 hours for Jan) So, as I read across the worksheet, I see the the project requires 1 Planned Resource (Lead Chicken Plucker) for 40 hours in Jan. In a cell above January, I am trying to do a count (not a sum) of resources who have hours allocated in January. Ideally, a formula would look at Column A, confirm there is an entry (what the entry is is immaterial at this point, it is just confirming a "Planned Resource" versus an "Assigned Resource"), then look across to see if there is an entry in the same row in Column S. This would tell me that there is one Planned Resource needed in January. As for summing, I already have those formulas, using SUMIF functions. It's getting counts that is a pain. Column C has the same info, but is the listing of Assigned Resources and there is a cell above January for that count as well (it would be the same formula, just ref'ing to Column C instead of Column A). My head hurts again... ;) -- Greg "JP" wrote: Here's an array formula that will work =SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56 pm, Greg in CO <Greg in wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
I've seen other post the desired result from the fomula, so here it is, if
that helps: In the cell showing total Planned Resources with hours in Jan, I would have 3; in the cell for Assigned Resources with hours in Jan, I would have 1. I am thinking a sumproduct formula might work, but my brain is giving me data leak errors at the moment. Thanks! All help is appreaciated -- Greg "Greg in CO" wrote: The hours should be under Column S...... -- Greg "Greg in CO" wrote: Good idea! Here is some sample data: Column A - Planned Position Column C - Assigned Position Column S - Jan Row 1 - Lead Chicken Plucker 40 Row 2 - Feather Sweeper 20 Row 3 - Beak Tweeker 10 Row 4------------------------------Assistant Beak Tweeker 30 So, this shows that: I have 3 planned Positions (no warm bodies to fill them yet) and 1 Assigned position (an actual person has been assigned). Hours estimated for these folks are listed in Columns S. I would like to show in two different cells above the January entry: How many Planned Positions have hours in January How many Assigned Positions have hours in January This would then be carried across the entire sheet, showing a count Planned and Assigned resources for each month. Ow! the pain in the head!......... ;) THis is part of a larger reosurce planning and allocation worksheet. -- Greg "JP" wrote: Why doesn't =COUNTIF(MyRange,TargetCell) work? Perhaps you should explain what you mean by "didn't work". You should also consider posting a small sample of data, what specific formulas you used that didn't work, your expected outcome, Excel version, etc. It sounds like you are trying to use Excel to do Project Management. You should probably use MS Project for that, it's much better suited for the job of resource planning and scheduling. --JP On Aug 12, 4:10 pm, Greg in CO wrote: Thanks JP...I tried that, but ti didn't work. I may not have been detailed enough in my original description. Column A (Planned Resource) will have a job description (i.e. Lead Chicken Plucker) Column S will have an entry for hours (i.e. 40 hours for Jan) So, as I read across the worksheet, I see the the project requires 1 Planned Resource (Lead Chicken Plucker) for 40 hours in Jan. In a cell above January, I am trying to do a count (not a sum) of resources who have hours allocated in January. Ideally, a formula would look at Column A, confirm there is an entry (what the entry is is immaterial at this point, it is just confirming a "Planned Resource" versus an "Assigned Resource"), then look across to see if there is an entry in the same row in Column S. This would tell me that there is one Planned Resource needed in January. As for summing, I already have those formulas, using SUMIF functions. It's getting counts that is a pain. Column C has the same info, but is the listing of Assigned Resources and there is a cell above January for that count as well (it would be the same formula, just ref'ing to Column C instead of Column A). My head hurts again... ;) -- Greg "JP" wrote: Here's an array formula that will work =SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56 pm, Greg in CO <Greg in wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
You seem to have brain issues Greg.
Sumproduct is probably the answer, but I'm having trouble understanding your data in order to apply it. The best I can do right now is point you to this site which explains how to use it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html ps- If you have dynamic named ranges it makes it much easier to construct the sumproduct formula. HTH, JP On Aug 12, 5:32 pm, Greg in CO wrote: I've seen other post the desired result from the fomula, so here it is, if that helps: In the cell showing total Planned Resources with hours in Jan, I would have 3; in the cell for Assigned Resources with hours in Jan, I would have 1. I am thinking a sumproduct formula might work, but my brain is giving me data leak errors at the moment. Thanks! All help is appreaciated -- Greg "Greg in CO" wrote: The hours should be under Column S...... -- Greg "Greg in CO" wrote: Good idea! Here is some sample data: Column A - Planned Position Column C - Assigned Position Column S - Jan Row 1 - Lead Chicken Plucker 40 Row 2 - Feather Sweeper 20 Row 3 - Beak Tweeker 10 Row 4------------------------------Assistant Beak Tweeker 30 So, this shows that: I have 3 planned Positions (no warm bodies to fill them yet) and 1 Assigned position (an actual person has been assigned). Hours estimated for these folks are listed in Columns S. I would like to show in two different cells above the January entry: How many Planned Positions have hours in January How many Assigned Positions have hours in January This would then be carried across the entire sheet, showing a count Planned and Assigned resources for each month. Ow! the pain in the head!......... ;) THis is part of a larger reosurce planning and allocation worksheet. -- Greg "JP" wrote: Why doesn't =COUNTIF(MyRange,TargetCell) work? Perhaps you should explain what you mean by "didn't work". You should also consider posting a small sample of data, what specific formulas you used that didn't work, your expected outcome, Excel version, etc. It sounds like you are trying to use Excel to do Project Management. You should probably use MS Project for that, it's much better suited for the job of resource planning and scheduling. --JP On Aug 12, 4:10 pm, Greg in CO wrote: Thanks JP...I tried that, but ti didn't work. I may not have been detailed enough in my original description. Column A (Planned Resource) will have a job description (i.e. Lead Chicken Plucker) Column S will have an entry for hours (i.e. 40 hours for Jan) So, as I read across the worksheet, I see the the project requires 1 Planned Resource (Lead Chicken Plucker) for 40 hours in Jan. In a cell above January, I am trying to do a count (not a sum) of resources who have hours allocated in January. Ideally, a formula would look at Column A, confirm there is an entry (what the entry is is immaterial at this point, it is just confirming a "Planned Resource" versus an "Assigned Resource"), then look across to see if there is an entry in the same row in Column S. This would tell me that there is one Planned Resource needed in January. As for summing, I already have those formulas, using SUMIF functions. It's getting counts that is a pain. Column C has the same info, but is the listing of Assigned Resources and there is a cell above January for that count as well (it would be the same formula, just ref'ing to Column C instead of Column A). My head hurts again... ;) -- Greg "JP" wrote: Here's an array formula that will work =SUM(IF(TargetCell=MyRange,1,0)) Where TargetCell = cell in column S you want to look up MyRange = range of cells you want to search Keep in mind it's an array formula so it will slow down your worksheet if you use too large a range, or use too many array formulas. For one or two it won't be too bad. HTH, JP On Aug 12, 2:56 pm, Greg in CO <Greg in wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Hey Greg,
Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Close, but no cookie. The COUNTA formulas work fine, but do not reflect the
realtionship between an entry for a Position and that position having hours in Jan. Let me try explaining the data again. Column A has a list of Planned Positions Column S is where the user enters the planned hours for the month of Jan (Column T is Feb, etc.) I am trying to find a formula that will look at Column A and confirm it is not blank, and then go across on the same row and count the non-blank cell on Column S. The end goal is "How many Planned Positions have hours in Jan?" I am not looking for a sum of the hours, but a count of the hours entries for Jan for Planned Positions. Using the positions I posted earlier: Column A Column S (Jan) Lead Chicken Plucker 40 Feather Sweeper 20 Beak Tweeker 10 I would have the following results: 3 planned resources have hours in Jan. This is important as, should there be an entry in Column a, but there are no hours in Column S, then that resource would not be counted as having hours in Jan. If anyone is game for me to email them a copy of this sheet, i am happy to do so. I looked at the SUMPRODUCT site in the UK - great info, but I didn't see a formula which solved the problem.....at least from what I could see. Again, any help is appreciated. -- Greg "tech1NJ" wrote: Hey Greg, Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
=SUMPRODUCT(A2:A50<""),--(S2:S50<""))
-- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Close, but no cookie. The COUNTA formulas work fine, but do not reflect the realtionship between an entry for a Position and that position having hours in Jan. Let me try explaining the data again. Column A has a list of Planned Positions Column S is where the user enters the planned hours for the month of Jan (Column T is Feb, etc.) I am trying to find a formula that will look at Column A and confirm it is not blank, and then go across on the same row and count the non-blank cell on Column S. The end goal is "How many Planned Positions have hours in Jan?" I am not looking for a sum of the hours, but a count of the hours entries for Jan for Planned Positions. Using the positions I posted earlier: Column A Column S (Jan) Lead Chicken Plucker 40 Feather Sweeper 20 Beak Tweeker 10 I would have the following results: 3 planned resources have hours in Jan. This is important as, should there be an entry in Column a, but there are no hours in Column S, then that resource would not be counted as having hours in Jan. If anyone is game for me to email them a copy of this sheet, i am happy to do so. I looked at the SUMPRODUCT site in the UK - great info, but I didn't see a formula which solved the problem.....at least from what I could see. Again, any help is appreciated. -- Greg "tech1NJ" wrote: Hey Greg, Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Thanks Peo, but that did not quite work. It returned a result of "0", even
though A12 and S12 both had entries...so it should have counted S12 as "1". Here is the formula with my actual ranges (FYI, Excel said there was a parens missing from the one you posted): =SUMPRODUCT((A12:A34<""),--(S12:S34<"")) -- Greg "Peo Sjoblom" wrote: =SUMPRODUCT(A2:A50<""),--(S2:S50<"")) -- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Close, but no cookie. The COUNTA formulas work fine, but do not reflect the realtionship between an entry for a Position and that position having hours in Jan. Let me try explaining the data again. Column A has a list of Planned Positions Column S is where the user enters the planned hours for the month of Jan (Column T is Feb, etc.) I am trying to find a formula that will look at Column A and confirm it is not blank, and then go across on the same row and count the non-blank cell on Column S. The end goal is "How many Planned Positions have hours in Jan?" I am not looking for a sum of the hours, but a count of the hours entries for Jan for Planned Positions. Using the positions I posted earlier: Column A Column S (Jan) Lead Chicken Plucker 40 Feather Sweeper 20 Beak Tweeker 10 I would have the following results: 3 planned resources have hours in Jan. This is important as, should there be an entry in Column a, but there are no hours in Column S, then that resource would not be counted as having hours in Jan. If anyone is game for me to email them a copy of this sheet, i am happy to do so. I looked at the SUMPRODUCT site in the UK - great info, but I didn't see a formula which solved the problem.....at least from what I could see. Again, any help is appreciated. -- Greg "tech1NJ" wrote: Hey Greg, Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Apologies, this is the correct one
=SUMPRODUCT(--(A12:A34<""),--(S12:S34<"")) it should count correctly -- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Thanks Peo, but that did not quite work. It returned a result of "0", even though A12 and S12 both had entries...so it should have counted S12 as "1". Here is the formula with my actual ranges (FYI, Excel said there was a parens missing from the one you posted): =SUMPRODUCT((A12:A34<""),--(S12:S34<"")) -- Greg "Peo Sjoblom" wrote: =SUMPRODUCT(A2:A50<""),--(S2:S50<"")) -- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Close, but no cookie. The COUNTA formulas work fine, but do not reflect the realtionship between an entry for a Position and that position having hours in Jan. Let me try explaining the data again. Column A has a list of Planned Positions Column S is where the user enters the planned hours for the month of Jan (Column T is Feb, etc.) I am trying to find a formula that will look at Column A and confirm it is not blank, and then go across on the same row and count the non-blank cell on Column S. The end goal is "How many Planned Positions have hours in Jan?" I am not looking for a sum of the hours, but a count of the hours entries for Jan for Planned Positions. Using the positions I posted earlier: Column A Column S (Jan) Lead Chicken Plucker 40 Feather Sweeper 20 Beak Tweeker 10 I would have the following results: 3 planned resources have hours in Jan. This is important as, should there be an entry in Column a, but there are no hours in Column S, then that resource would not be counted as having hours in Jan. If anyone is game for me to email them a copy of this sheet, i am happy to do so. I looked at the SUMPRODUCT site in the UK - great info, but I didn't see a formula which solved the problem.....at least from what I could see. Again, any help is appreciated. -- Greg "tech1NJ" wrote: Hey Greg, Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
Whoo-hoo! After looking at the formula Peo sent me...and having read copius
posts about the SUMPRODUCT formula variations...I had a "Hmmmm" moment and wondered what I would get if I put the "--" in front of the first array...and it worked! Yay! (Happy dance, happy dance). My thanks! to all the folks who took the time to help...you guys are great!!!!! Here is the final formula: =SUMPRODUCT(--(A12:A34<""),--(S12:S34<"")), then press CSE to commit. Yipee! -- Greg "Peo Sjoblom" wrote: =SUMPRODUCT(A2:A50<""),--(S2:S50<"")) -- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Close, but no cookie. The COUNTA formulas work fine, but do not reflect the realtionship between an entry for a Position and that position having hours in Jan. Let me try explaining the data again. Column A has a list of Planned Positions Column S is where the user enters the planned hours for the month of Jan (Column T is Feb, etc.) I am trying to find a formula that will look at Column A and confirm it is not blank, and then go across on the same row and count the non-blank cell on Column S. The end goal is "How many Planned Positions have hours in Jan?" I am not looking for a sum of the hours, but a count of the hours entries for Jan for Planned Positions. Using the positions I posted earlier: Column A Column S (Jan) Lead Chicken Plucker 40 Feather Sweeper 20 Beak Tweeker 10 I would have the following results: 3 planned resources have hours in Jan. This is important as, should there be an entry in Column a, but there are no hours in Column S, then that resource would not be counted as having hours in Jan. If anyone is game for me to email them a copy of this sheet, i am happy to do so. I looked at the SUMPRODUCT site in the UK - great info, but I didn't see a formula which solved the problem.....at least from what I could see. Again, any help is appreciated. -- Greg "tech1NJ" wrote: Hey Greg, Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To Count or not to Count
No need to enter with ctrl + shift & enter, that's the beauty of SUMPRODUCT
-- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Whoo-hoo! After looking at the formula Peo sent me...and having read copius posts about the SUMPRODUCT formula variations...I had a "Hmmmm" moment and wondered what I would get if I put the "--" in front of the first array...and it worked! Yay! (Happy dance, happy dance). My thanks! to all the folks who took the time to help...you guys are great!!!!! Here is the final formula: =SUMPRODUCT(--(A12:A34<""),--(S12:S34<"")), then press CSE to commit. Yipee! -- Greg "Peo Sjoblom" wrote: =SUMPRODUCT(A2:A50<""),--(S2:S50<"")) -- Regards, Peo Sjoblom "Greg in CO" wrote in message ... Close, but no cookie. The COUNTA formulas work fine, but do not reflect the realtionship between an entry for a Position and that position having hours in Jan. Let me try explaining the data again. Column A has a list of Planned Positions Column S is where the user enters the planned hours for the month of Jan (Column T is Feb, etc.) I am trying to find a formula that will look at Column A and confirm it is not blank, and then go across on the same row and count the non-blank cell on Column S. The end goal is "How many Planned Positions have hours in Jan?" I am not looking for a sum of the hours, but a count of the hours entries for Jan for Planned Positions. Using the positions I posted earlier: Column A Column S (Jan) Lead Chicken Plucker 40 Feather Sweeper 20 Beak Tweeker 10 I would have the following results: 3 planned resources have hours in Jan. This is important as, should there be an entry in Column a, but there are no hours in Column S, then that resource would not be counted as having hours in Jan. If anyone is game for me to email them a copy of this sheet, i am happy to do so. I looked at the SUMPRODUCT site in the UK - great info, but I didn't see a formula which solved the problem.....at least from what I could see. Again, any help is appreciated. -- Greg "tech1NJ" wrote: Hey Greg, Try this shot of penecilin. In this example Planned Position is in column B11, Assigned Position is in column C11, Hours Assigned in Column S12. First formula is in cell B10 and the other in C10. As the Dr. will say, "Take this medicine and I wil call yo in the morning". :) Jan =COUNTA(B12:B19) =COUNTA(C12:C19) Column S Column A - Planned Position Column C - Assigned Position 40 Row 1 - Lead Chicken Plucker 20 Row 2 - Feather Sweeper 10 Row 3 - Beak Tweeker 30 Row 4- Assistant Beak Tweeker -- tech1NJ "Greg in CO" wrote: I have a resource planning sheet. In column A, I have a drop menu which allows the user to select a job position which the think they may need. In column S, they can put the estimate hour for that job. Column S is for January. I would like to count the entires in column S which equate to an entry in column A. I have tried countif, counta, nested if formulas...I am stumped. The logic should be "if there are entries in column A, count the corresponding entries in column S. Also, there is the same drop menu in column C, which allows the user to indicate that they have an actual resource with the desire job position assigned, and then the estimate or actual hours for that assigned resource would also go into column S (or for in the column for the month in question). So, column S could have entries for planned resources and for assigned resources. Any help is appreciated...my brain hurts at the moment. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |