I seek help to find a formula to do the following:
I have a range (P2:AB659) that is populated with dates entered 1/16/06
format and displayed as 6-Jan format. I need to count the number of cells
containing any date in January, any date in February, etc.
TIA, Matt
=SUMPRODUCT(--(MONTH(P2:AB659)=1)
change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
etc.)
quot;Matt7102quot; wrote:
gt; I seek help to find a formula to do the following:
gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; containing any date in January, any date in February, etc.
gt;
gt; TIA, Matt
gt;
gt;
What is the purpose/meaning of the double dash (--) in this formula?
Thanks
quot;Slothquot; wrote:
gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt;
gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; etc.)
gt;
gt; quot;Matt7102quot; wrote:
gt;
gt; gt; I seek help to find a formula to do the following:
gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; containing any date in January, any date in February, etc.
gt; gt;
gt; gt; TIA, Matt
gt; gt;
gt; gt;
It turns an array of logical terms into 1's and 0's. This way they can be
summed (TRUE/FALSE is ignored when summing).
Example:
{TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
NOTE: The formula is missing a close parenthesis and should be
=SUMPRODUCT(--(MONTH(P2:AB659)=1))
quot;Bean123rquot; wrote:
gt; What is the purpose/meaning of the double dash (--) in this formula?
gt;
gt; Thanks
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt;
gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; etc.)
gt; gt;
gt; gt; quot;Matt7102quot; wrote:
gt; gt;
gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt;
gt; gt; gt; TIA, Matt
gt; gt; gt;
gt; gt; gt;
Thanks for the help, and the short tutorial on explaining the formula
function... however, using a smaller dataset to test, the formula does not
return the correct result for January. Works fine for all other months.
When I add a date in the range other than January to the test dataset, (or
delete an existing) all is well for the month altered. January is just NOT
working...any ideas?
quot;Slothquot; wrote:
gt; It turns an array of logical terms into 1's and 0's. This way they can be
gt; summed (TRUE/FALSE is ignored when summing).
gt;
gt; Example:
gt; {TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
gt;
gt; NOTE: The formula is missing a close parenthesis and should be
gt;
gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1))
gt;
gt; quot;Bean123rquot; wrote:
gt;
gt; gt; What is the purpose/meaning of the double dash (--) in this formula?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt; gt;
gt; gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; gt; etc.)
gt; gt; gt;
gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt; gt;
gt; gt; gt; gt; TIA, Matt
gt; gt; gt; gt;
gt; gt; gt; gt;
I have no idea why it wouldn't be working. I made a small list to test that
worked fine. Can you show the list, formulas, and results? Is it giving an
error, or the wrong value? What value does the formula produce? What value
should it be?
quot;Matt7102quot; wrote:
gt; Thanks for the help, and the short tutorial on explaining the formula
gt; function... however, using a smaller dataset to test, the formula does not
gt; return the correct result for January. Works fine for all other months.
gt; When I add a date in the range other than January to the test dataset, (or
gt; delete an existing) all is well for the month altered. January is just NOT
gt; working...any ideas?
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; It turns an array of logical terms into 1's and 0's. This way they can be
gt; gt; summed (TRUE/FALSE is ignored when summing).
gt; gt;
gt; gt; Example:
gt; gt; {TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
gt; gt;
gt; gt; NOTE: The formula is missing a close parenthesis and should be
gt; gt;
gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1))
gt; gt;
gt; gt; quot;Bean123rquot; wrote:
gt; gt;
gt; gt; gt; What is the purpose/meaning of the double dash (--) in this formula?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt; gt; gt;
gt; gt; gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; gt; gt; etc.)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; TIA, Matt
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
Well, I cannot attach the data set, so here is an example: With only a
single column of the range populated:
P
2) 1-Jan =SUMPRODUCT(--(MONTH($P$2:$Y$14)=1))
3) 1-Feb =SUMPRODUCT(--(MONTH($P$2:$Y$14)=2))
4) 1-Mar =SUMPRODUCT(--(MONTH($P$2:$Y$14)=3))
Etc thru 1-Dec, formula contnues as well. Jan returns 119, all others
return 1. If I change 1-Jan to 1-Jun, June formula result changes to 2, and
January changes to 118. If I change all 12 to 1-Jan, January result is 130,
all other results are zero. If I then clear contents, Jan result is 130, all
other results are zero.
Thanks again, Matt
quot;Slothquot; wrote:
gt; I have no idea why it wouldn't be working. I made a small list to test that
gt; worked fine. Can you show the list, formulas, and results? Is it giving an
gt; error, or the wrong value? What value does the formula produce? What value
gt; should it be?
gt;
gt; quot;Matt7102quot; wrote:
gt;
gt; gt; Thanks for the help, and the short tutorial on explaining the formula
gt; gt; function... however, using a smaller dataset to test, the formula does not
gt; gt; return the correct result for January. Works fine for all other months.
gt; gt; When I add a date in the range other than January to the test dataset, (or
gt; gt; delete an existing) all is well for the month altered. January is just NOT
gt; gt; working...any ideas?
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt; gt; It turns an array of logical terms into 1's and 0's. This way they can be
gt; gt; gt; summed (TRUE/FALSE is ignored when summing).
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt; {TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
gt; gt; gt;
gt; gt; gt; NOTE: The formula is missing a close parenthesis and should be
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1))
gt; gt; gt;
gt; gt; gt; quot;Bean123rquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; What is the purpose/meaning of the double dash (--) in this formula?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; gt; gt; gt; etc.)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; TIA, Matt
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
I just figured out that the cell range is 130 cells, and entering any date
reduces the lt;blankgt; cell count by one- Jan formula is counting blank cells
perhaps?
quot;Slothquot; wrote:
gt; I have no idea why it wouldn't be working. I made a small list to test that
gt; worked fine. Can you show the list, formulas, and results? Is it giving an
gt; error, or the wrong value? What value does the formula produce? What value
gt; should it be?
gt;
gt; quot;Matt7102quot; wrote:
gt;
gt; gt; Thanks for the help, and the short tutorial on explaining the formula
gt; gt; function... however, using a smaller dataset to test, the formula does not
gt; gt; return the correct result for January. Works fine for all other months.
gt; gt; When I add a date in the range other than January to the test dataset, (or
gt; gt; delete an existing) all is well for the month altered. January is just NOT
gt; gt; working...any ideas?
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt; gt; It turns an array of logical terms into 1's and 0's. This way they can be
gt; gt; gt; summed (TRUE/FALSE is ignored when summing).
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt; {TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
gt; gt; gt;
gt; gt; gt; NOTE: The formula is missing a close parenthesis and should be
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1))
gt; gt; gt;
gt; gt; gt; quot;Bean123rquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; What is the purpose/meaning of the double dash (--) in this formula?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; gt; gt; gt; etc.)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; TIA, Matt
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
Good problem solving, I didn't realise you had blank cells. Try these
formulas.
=SUMPRODUCT(--(MONTH(range)=1),1-(ISBLANK(range)))
or
=SUMPRODUCT(--(MONTH(range)=1),--(ISNUMBER(range)))
quot;Matt7102quot; wrote:
gt; I just figured out that the cell range is 130 cells, and entering any date
gt; reduces the lt;blankgt; cell count by one- Jan formula is counting blank cells
gt; perhaps?
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; I have no idea why it wouldn't be working. I made a small list to test that
gt; gt; worked fine. Can you show the list, formulas, and results? Is it giving an
gt; gt; error, or the wrong value? What value does the formula produce? What value
gt; gt; should it be?
gt; gt;
gt; gt; quot;Matt7102quot; wrote:
gt; gt;
gt; gt; gt; Thanks for the help, and the short tutorial on explaining the formula
gt; gt; gt; function... however, using a smaller dataset to test, the formula does not
gt; gt; gt; return the correct result for January. Works fine for all other months.
gt; gt; gt; When I add a date in the range other than January to the test dataset, (or
gt; gt; gt; delete an existing) all is well for the month altered. January is just NOT
gt; gt; gt; working...any ideas?
gt; gt; gt;
gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; It turns an array of logical terms into 1's and 0's. This way they can be
gt; gt; gt; gt; summed (TRUE/FALSE is ignored when summing).
gt; gt; gt; gt;
gt; gt; gt; gt; Example:
gt; gt; gt; gt; {TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
gt; gt; gt; gt;
gt; gt; gt; gt; NOTE: The formula is missing a close parenthesis and should be
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1))
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bean123rquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; What is the purpose/meaning of the double dash (--) in this formula?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; gt; gt; gt; gt; etc.)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; gt; gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; gt; gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; gt; gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; TIA, Matt
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
Thanks for the assistance, it now works without error. Hope I can pass along
similar help someday!
Matt
quot;Slothquot; wrote:
gt; Good problem solving, I didn't realise you had blank cells. Try these
gt; formulas.
gt;
gt; =SUMPRODUCT(--(MONTH(range)=1),1-(ISBLANK(range)))
gt; or
gt; =SUMPRODUCT(--(MONTH(range)=1),--(ISNUMBER(range)))
gt;
gt; quot;Matt7102quot; wrote:
gt;
gt; gt; I just figured out that the cell range is 130 cells, and entering any date
gt; gt; reduces the lt;blankgt; cell count by one- Jan formula is counting blank cells
gt; gt; perhaps?
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt; gt; I have no idea why it wouldn't be working. I made a small list to test that
gt; gt; gt; worked fine. Can you show the list, formulas, and results? Is it giving an
gt; gt; gt; error, or the wrong value? What value does the formula produce? What value
gt; gt; gt; should it be?
gt; gt; gt;
gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Thanks for the help, and the short tutorial on explaining the formula
gt; gt; gt; gt; function... however, using a smaller dataset to test, the formula does not
gt; gt; gt; gt; return the correct result for January. Works fine for all other months.
gt; gt; gt; gt; When I add a date in the range other than January to the test dataset, (or
gt; gt; gt; gt; delete an existing) all is well for the month altered. January is just NOT
gt; gt; gt; gt; working...any ideas?
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; It turns an array of logical terms into 1's and 0's. This way they can be
gt; gt; gt; gt; gt; summed (TRUE/FALSE is ignored when summing).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Example:
gt; gt; gt; gt; gt; {TRUE,TRUE,FALSE,TRUE}-gt;{1,1,0,1}
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; NOTE: The formula is missing a close parenthesis and should be
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Bean123rquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; What is the purpose/meaning of the double dash (--) in this formula?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Slothquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; =SUMPRODUCT(--(MONTH(P2:AB659)=1)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; change the 1 to the month number you want to count (ie. Jan=1, Feb=2, Mar=3,
gt; gt; gt; gt; gt; gt; gt; etc.)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;Matt7102quot; wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I seek help to find a formula to do the following:
gt; gt; gt; gt; gt; gt; gt; gt; I have a range (P2:AB659) that is populated with dates entered 1/16/06
gt; gt; gt; gt; gt; gt; gt; gt; format and displayed as 6-Jan format. I need to count the number of cells
gt; gt; gt; gt; gt; gt; gt; gt; containing any date in January, any date in February, etc.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; TIA, Matt
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt;
留言列表
