Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
I am working on an end of month report.
So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
I assume the months will be in numbers, not text. So 1 = January, 2 =
February, etc. Let's say C1 = 1, to indicate January. So what you could so is use: =SUMIF(B:B, C1, D:D) D:D contains the cells of what you want to sum. Or if you just want a count, then you can just use COUNTIF. Hope that helps. "Mike Pearson" wrote: I am working on an end of month report. So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
I tried to use =countif((month(B1:B20),month(where the second page cell is))
but that didn't result in the right answer. What I need is for the formula to review a whole column (I can't type each cell in because month to month I add many new accounts) against today's month to show how many I have visited this month? "Harimau" wrote: I assume the months will be in numbers, not text. So 1 = January, 2 = February, etc. Let's say C1 = 1, to indicate January. So what you could so is use: =SUMIF(B:B, C1, D:D) D:D contains the cells of what you want to sum. Or if you just want a count, then you can just use COUNTIF. Hope that helps. "Mike Pearson" wrote: I am working on an end of month report. So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
=SUMPRODUCT(--(MONTH(Sheet1!B2:B100)=MONTH(A1)))
"Mike Pearson" wrote: I tried to use =countif((month(B1:B20),month(where the second page cell is)) but that didn't result in the right answer. What I need is for the formula to review a whole column (I can't type each cell in because month to month I add many new accounts) against today's month to show how many I have visited this month? "Harimau" wrote: I assume the months will be in numbers, not text. So 1 = January, 2 = February, etc. Let's say C1 = 1, to indicate January. So what you could so is use: =SUMIF(B:B, C1, D:D) D:D contains the cells of what you want to sum. Or if you just want a count, then you can just use COUNTIF. Hope that helps. "Mike Pearson" wrote: I am working on an end of month report. So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
One more if you care about the year, too:
=SUMPRODUCT(--(TEXT(Sheet1!B2:B100,"yyyymm")=TEXT(A1,"yyyymm"))) or if you always want it for the current month: =SUMPRODUCT(--(TEXT(Sheet1!B2:B100,"yyyymm")=TEXT(today(),"yyyym m"))) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Mike Pearson wrote: I am working on an end of month report. So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
That still doesn't seem to be doing it. I keep getting zero? Currently
there is a list of 10 accounts. Some are this month (5/24/07, 5/23/07) and some are last month, and some are blank. I don't know what to do. I think the issue is using month to read the whole column. Since there are many different months it doesn't seem to work right. If I make a cell =month(sheet1!B1:B100) it gives me an error that it is the wrong data type? Any other thoughts? I really appreciate the help. "Roger Govier" wrote: Hi Mike Try =SUMPRODUCT(--(Month(Sheet1!$A$2:$A$100)=MONTH(A1))) where A1 on Sheet 2 holds the date for the Month which data you wish to capture. -- Regards Roger Govier "Mike Pearson" <Mike wrote in message ... I am working on an end of month report. So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a column of cells to add if current month
Hi Mike
That should work. Yes you will get an error with =MONTH(Sheet1!B1:B100), but when it is passed to Sumproduct, it will be treated as an array. Are you sure that your date are true Excel dates and not Text? What do you get if you just use =MONTH(B1)? Three of us have given you basically the same formula, Dave's has the advantage that it will deal with Year as well as Month. It is a technique that works, so I think there must be something wrong with the data. -- Regards Roger Govier "Mike Pearson" wrote in message ... That still doesn't seem to be doing it. I keep getting zero? Currently there is a list of 10 accounts. Some are this month (5/24/07, 5/23/07) and some are last month, and some are blank. I don't know what to do. I think the issue is using month to read the whole column. Since there are many different months it doesn't seem to work right. If I make a cell =month(sheet1!B1:B100) it gives me an error that it is the wrong data type? Any other thoughts? I really appreciate the help. "Roger Govier" wrote: Hi Mike Try =SUMPRODUCT(--(Month(Sheet1!$A$2:$A$100)=MONTH(A1))) where A1 on Sheet 2 holds the date for the Month which data you wish to capture. -- Regards Roger Govier "Mike Pearson" <Mike wrote in message ... I am working on an end of month report. So on one sheet I have a list of accounts, with one column that has dates for the last appointment I had with those accounts. Etc: Account Name Last Visit 1. ABC Mortgage 5/15/2007 2. XWZ Mortgage 5/16/2007 On another sheet I have a report that asks "how many accounts I visited this month". What I want to do is have the date on the top of the page and then make a formula that adds all the cells whose dates fall in the month on the top of the second sheet? I have been able to use today() to get the date on the top of the page, but can't figure out how to then reference "last visit" to add up "how many accounts I visited this month" Can you help me please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells of a particular month in a column of dates | Excel Discussion (Misc queries) | |||
Calculate the first day of the month for the current month? | Excel Discussion (Misc queries) | |||
Current Month | Excel Worksheet Functions | |||
identifying current month | Charts and Charting in Excel | |||
Current Month Query | Excel Worksheet Functions |