Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
I have a list of data (1000 rows). Each row has several columns containing information on a given transaction. column F contains the date of the transaction (mm/dd/yyyy format.) column M contains a dollar amount. I have two formulas that I need to make that i am not sure how to handle. 1. I want formula that will count all the cells in column F that have a month of January and give me a total for the number of rows that contain this. I tried: =COUNTIF(F2:F1000,MONTH(1)) but it did not work. 2. I want a formula that will add up the sum ($) of all the cells in column M if the month in column F is January. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
Try these array formulas, I'm sure that SumProduct could do it but I
suck at using SumProduct. 1. =SUM(IF(MONTH(F2:F1000)=1,1,0)) 2. =SUM(IF(MONTH(F2:F1000)=1,M2:1000,0)) Make sure to press Ctrl+Shift+Enter after entering the formula into the cell Charles jermsalerms wrote: I have a list of data (1000 rows). Each row has several columns containing information on a given transaction. column F contains the date of the transaction (mm/dd/yyyy format.) column M contains a dollar amount. I have two formulas that I need to make that i am not sure how to handle. 1. I want formula that will count all the cells in column F that have a month of January and give me a total for the number of rows that contain this. I tried: =COUNTIF(F2:F1000,MONTH(1)) but it did not work. 2. I want a formula that will add up the sum ($) of all the cells in column M if the month in column F is January. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
the first one only returns a "1" even though there are about twenty with this month. The 2nd one says there is an error in the formula. Thanks for the suggestion. Does anyone else have an idea. PS - I forgot to say that month must be Jan. and year must be 2006 in both formulas. I dont want to count transactions from Jan of 2005 or 2004 etc. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
Why not use SUMIF function?
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Die_Another_Day" wrote in message oups.com... Try these array formulas, I'm sure that SumProduct could do it but I suck at using SumProduct. 1. =SUM(IF(MONTH(F2:F1000)=1,1,0)) 2. =SUM(IF(MONTH(F2:F1000)=1,M2:1000,0)) Make sure to press Ctrl+Shift+Enter after entering the formula into the cell Charles jermsalerms wrote: I have a list of data (1000 rows). Each row has several columns containing information on a given transaction. column F contains the date of the transaction (mm/dd/yyyy format.) column M contains a dollar amount. I have two formulas that I need to make that i am not sure how to handle. 1. I want formula that will count all the cells in column F that have a month of January and give me a total for the number of rows that contain this. I tried: =COUNTIF(F2:F1000,MONTH(1)) but it did not work. 2. I want a formula that will add up the sum ($) of all the cells in column M if the month in column F is January. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
=sumproduct(--(text(f2:f1000,"yyyymm")="200601"),m2:m1000)
Adjust the ranges to match--but you can't use whole columns. =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 jermsalerms wrote: I have a list of data (1000 rows). Each row has several columns containing information on a given transaction. column F contains the date of the transaction (mm/dd/yyyy format.) column M contains a dollar amount. I have two formulas that I need to make that i am not sure how to handle. 1. I want formula that will count all the cells in column F that have a month of January and give me a total for the number of rows that contain this. I tried: =COUNTIF(F2:F1000,MONTH(1)) but it did not work. 2. I want a formula that will add up the sum ($) of all the cells in column M if the month in column F is January. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
not sure how I wold write that. Do you know? -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning value if month is January
Nice!!! I did come up with another way to do it but this is much better and quicker -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167 View this thread: http://www.excelforum.com/showthread...hreadid=573187 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=DATEVALUE | Excel Worksheet Functions | |||
Workaround for returning month name? | Excel Worksheet Functions | |||
Returning the Week Number of a Specific Date on a Month | Excel Worksheet Functions | |||
Returning the Week Number of a Specific Date on a Month | Excel Worksheet Functions | |||
Returning the Week Number of a Specific Date on a Month | Excel Worksheet Functions |