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

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

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

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

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

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

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

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