Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
hi,
i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
Are your "dates" (the Jan-07 for example) text or real Excel dates? If they
are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
here is what i have now in cell IV1:
=SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2))) now i am getting a #value error. i noticed that when i paste your formula in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there. "Rick Rothstein (MVP - VB)" wrote: Are your "dates" (the Jan-07 for example) text or real Excel dates? If they are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
Yes, if you specify the whole row, Excel will change A1:IV1 to 1:1 (I put it
in the way I did so you could see how to change it if you were going to specify a smaller range than the entire row... the less cells being processed by SUMPRODUCT, the more efficient the calculation is). Did you try my formula **as I posted it**? If not, try it and then let us know the results (don't modify it as you showed you did in your last message; copy/paste it exactly as I wrote it). Rick "geebee" (noSPAMs) wrote in message ... here is what i have now in cell IV1: =SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2))) now i am getting a #value error. i noticed that when i paste your formula in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there. "Rick Rothstein (MVP - VB)" wrote: Are your "dates" (the Jan-07 for example) text or real Excel dates? If they are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
What's wrong with using the formula that Rick gave you?
Unless you have XL2007, you cannot use an entire row as a range for Sumproduct. You must use a specific range. So if your paste somehow "gets rid of the A1 and IV1", then put it back. If your range ends at DD1, then use: =Sumproduct(--(Year(a1:dd1)=Year(Now()))) Regards. Fred "geebee" (noSPAMs) wrote in message ... here is what i have now in cell IV1: =SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2))) now i am getting a #value error. i noticed that when i paste your formula in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there. "Rick Rothstein (MVP - VB)" wrote: Are your "dates" (the Jan-07 for example) text or real Excel dates? If they are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
Unless you have XL2007, you cannot use an entire row as a range for
Sumproduct. You must use a specific range. So if your paste somehow "gets rid of the A1 and IV1", then put it back. If your range ends at DD1, then use: Apparently you can... for rows. This works fine in my XL2003... =SUMPRODUCT(--(YEAR(1:2)=YEAR(NOW()))) where I modified the formula to look at rows 1 and 2. And Excel did change the A1:IV1 range to 1:1 automatically. Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
hi,
i tried: =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) in cell IV1, and i am getting an #value! error message. does it matter that some of the columns heading in row 1 are like "emp", "building name" and so forth, in addition to the date value columns? "Rick Rothstein (MVP - VB)" wrote: Yes, if you specify the whole row, Excel will change A1:IV1 to 1:1 (I put it in the way I did so you could see how to change it if you were going to specify a smaller range than the entire row... the less cells being processed by SUMPRODUCT, the more efficient the calculation is). Did you try my formula **as I posted it**? If not, try it and then let us know the results (don't modify it as you showed you did in your last message; copy/paste it exactly as I wrote it). Rick "geebee" (noSPAMs) wrote in message ... here is what i have now in cell IV1: =SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2))) now i am getting a #value error. i noticed that when i paste your formula in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there. "Rick Rothstein (MVP - VB)" wrote: Are your "dates" (the Jan-07 for example) text or real Excel dates? If they are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
You can't put the formula in a cell that includes that cell as part of the
formula's range. Either put the formula on a different row or, if you are going to put the formula in IV1, the change the range to A1:IU1 or, as I said in one of my other posts, to a smaller range (less cells in the range means the formula is more efficient). Rick "geebee" (noSPAMs) wrote in message ... hi, i tried: =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) in cell IV1, and i am getting an #value! error message. does it matter that some of the columns heading in row 1 are like "emp", "building name" and so forth, in addition to the date value columns? "Rick Rothstein (MVP - VB)" wrote: Yes, if you specify the whole row, Excel will change A1:IV1 to 1:1 (I put it in the way I did so you could see how to change it if you were going to specify a smaller range than the entire row... the less cells being processed by SUMPRODUCT, the more efficient the calculation is). Did you try my formula **as I posted it**? If not, try it and then let us know the results (don't modify it as you showed you did in your last message; copy/paste it exactly as I wrote it). Rick "geebee" (noSPAMs) wrote in message ... here is what i have now in cell IV1: =SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2))) now i am getting a #value error. i noticed that when i paste your formula in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there. "Rick Rothstein (MVP - VB)" wrote: Are your "dates" (the Jan-07 for example) text or real Excel dates? If they are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count occurences meeting criteria
does it matter that some of the columns heading in row 1
are like "emp", "building name" and so forth, in addition to the date value columns? Yes, that makes a difference and is probably why you're getting the error. YEAR("emp") = #VALUE! The YEAR function is expecting a date value. -- Biff Microsoft Excel MVP "geebee" (noSPAMs) wrote in message ... hi, i tried: =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) in cell IV1, and i am getting an #value! error message. does it matter that some of the columns heading in row 1 are like "emp", "building name" and so forth, in addition to the date value columns? "Rick Rothstein (MVP - VB)" wrote: Yes, if you specify the whole row, Excel will change A1:IV1 to 1:1 (I put it in the way I did so you could see how to change it if you were going to specify a smaller range than the entire row... the less cells being processed by SUMPRODUCT, the more efficient the calculation is). Did you try my formula **as I posted it**? If not, try it and then let us know the results (don't modify it as you showed you did in your last message; copy/paste it exactly as I wrote it). Rick "geebee" (noSPAMs) wrote in message ... here is what i have now in cell IV1: =SUMPRODUCT(--(RIGHT(YEAR(1:1),2)=RIGHT(YEAR(NOW()),2))) now i am getting a #value error. i noticed that when i paste your formula in as is, it gets rid of the A1 and IV1 and just puts 1:1 in there. "Rick Rothstein (MVP - VB)" wrote: Are your "dates" (the Jan-07 for example) text or real Excel dates? If they are real dates, you could use this... =SUMPRODUCT(--(YEAR(A1:IV1)=YEAR(NOW()))) If you have a maximum column that your data goes to, use that column designation in place of the IV column designation that I used. Rick "geebee" (noSPAMs) wrote in message ... hi, i have a row 1, and values across the columns in row 1 like "Jan-07" ... dates randing from "Jan-06" through the present month. I am trying to put a formula somewhere in the worksheet that counts the number of cells in the forst row that have a year equal to the current year. Here is what I have, as an array formula: =COUNT((RIGHT(YEAR(A1:DD1),2)))=RIGHT((YEAR(TODAY( ))),2) but it is returning FALSE what am i doing wrong? if possible i would like to use a countif or something else besides an array formula. thanks in advance, geebee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of entries meeting criteria | Excel Worksheet Functions | |||
Count occurences from one worksheet to another with "IF" Criteria | Excel Worksheet Functions | |||
How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
count records meeting three criteria | Excel Worksheet Functions | |||
COUNTIF MEETING TWO CRITERIA eg>5 AND <10.1 | Excel Worksheet Functions |