Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet criteria
I am attempting to add a formula in a separate spreadsheet that counts the
value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet criteria
You gave us no information about how your worksheet is set up. So, assuming
your Date/Amount data is on a sheet named Sheet1 and that the data starts in Row 2 with Column A containing the Dates and Column B containing the Amounts, put this formula on your other sheet... =SUMPRODUCT((Sheet1!A2:A1000=C2)*Sheet1!B2:B1000) Note that I further assumed the date you wanted the summation for was in C2 on the same sheet where you are putting the above formula. If you have more than a 1000 rows of data, change both 1000s to a row number that is equal to or larger than the maximum row you ever expect to have data in. If 1000 is too much, use a smaller number. -- Rick (MVP - Excel) "LuvJ1s" wrote in message ... I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet criteria
Hi
Try =SUMIF(A:A,--"12/31/08",B:B) or better still put your date required in a cell e.g. E1, then use =SUMIF(A:A,E1,B:B) -- Regards Roger Govier "LuvJ1s" wrote in message ... I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet criteria
With date in Column A, data in B, put the date you want to lookup in D and
this formula in E =SUMIF(A1:A5,D1,B1:B5) -- -John Please rate when your question is answered to help us and others know what is helpful. "LuvJ1s" wrote: I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet crit
This formula is precisely what was needed! Thank you for your prompt reply
and expertise. "Rick Rothstein" wrote: You gave us no information about how your worksheet is set up. So, assuming your Date/Amount data is on a sheet named Sheet1 and that the data starts in Row 2 with Column A containing the Dates and Column B containing the Amounts, put this formula on your other sheet... =SUMPRODUCT((Sheet1!A2:A1000=C2)*Sheet1!B2:B1000) Note that I further assumed the date you wanted the summation for was in C2 on the same sheet where you are putting the above formula. If you have more than a 1000 rows of data, change both 1000s to a row number that is equal to or larger than the maximum row you ever expect to have data in. If 1000 is too much, use a smaller number. -- Rick (MVP - Excel) "LuvJ1s" wrote in message ... I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet crit
This worked perfectly! Thank you for your expertise and prompt reply.
"Roger Govier" wrote: Hi Try =SUMIF(A:A,--"12/31/08",B:B) or better still put your date required in a cell e.g. E1, then use =SUMIF(A:A,E1,B:B) -- Regards Roger Govier "LuvJ1s" wrote in message ... I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet crit
Thank you! This formula works perfectly. I appreciate your prompt reply and
expertise. "John Bundy" wrote: With date in Column A, data in B, put the date you want to lookup in D and this formula in E =SUMIF(A1:A5,D1,B1:B5) -- -John Please rate when your question is answered to help us and others know what is helpful. "LuvJ1s" wrote: I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formular to Count values in cells of rows that meet criteria
another way,
assuming that date are in Column A and value in Column B, enter the date you want to lookup in C2 and copy and paste either of these array formula in D2, confirm by Ctrl, Shift & Enter Excel will automatically put a curly bracket {.....} wrap around the formula, note that you do not need to type the curly bracket, if you have press Enter accidentally after entering the formula, just go back to the cell and in edit mode reenter again. =SUM((--$A$2:$A$5=C2)*--($B$2:$B$5)) or =SUM(IF($A$2:$A$5=C2,$B$2:$B$5,0)) HTH -- Pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "LuvJ1s" wrote: I am attempting to add a formula in a separate spreadsheet that counts the value in existing cells if the row begins with a certain date. For example, I would like the spreadsheet to return the 'Amount' received on 12/31/2008 (15.00) and disregard all other dates. Date Amount 12/31/2008 5.00 12/31/2008 10.00 1/1/2009 2.00 1/1/2009 4.00 1/2/2009 1.00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count cells that meet 2 criteria | Excel Worksheet Functions | |||
Count the number of values in a list that meet certain criteria | Excel Worksheet Functions | |||
count rows that meet certain criteria | Excel Discussion (Misc queries) | |||
Count Cells that meet Criteria | Excel Worksheet Functions | |||
count rows that meet two criteria in two different columns? | Excel Worksheet Functions |