Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default Sumif, SumProduct, CountIf Formula Help

I need help with two formulas. The first is I want a formula that would take
column A below and add the totals that are in column B that are between
3/1/08 and 3/24/08 in column A. The total I would like in C1. Also is
there a formula that can be written to take the date on the computer back 10
days and use column A to give a total in B that is in this date range?

Thank you for your help.


A B C
1 3/23/08 26.48
2 4/20/08 0.00
3 4/16/08 2.48
4 3/18/08 46.81
5 3/10/08 0.00
6 4/12/08 42.00

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sumif, SumProduct, CountIf Formula Help

=SUMPRODUCT(IF(AND(A1:A6=DATEVALUE("3/1/2008"),A1:A6<=DATEVALUE("3/24/2008")),1,0)*B1:B6)

To get 10 days earlier subtract 10 from the dates

=SUMPRODUCT(IF(AND(A1:A6=(DATEVALUE("3/1/2008")-10),A1:A6<=(DATEVALUE("3/24/2008")-10),1,0)*B1:B6)


"Jeremy" wrote:

I need help with two formulas. The first is I want a formula that would take
column A below and add the totals that are in column B that are between
3/1/08 and 3/24/08 in column A. The total I would like in C1. Also is
there a formula that can be written to take the date on the computer back 10
days and use column A to give a total in B that is in this date range?

Thank you for your help.


A B C
1 3/23/08 26.48
2 4/20/08 0.00
3 4/16/08 2.48
4 3/18/08 46.81
5 3/10/08 0.00
6 4/12/08 42.00

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Sumif, SumProduct, CountIf Formula Help

First formula - put this in C1:

=SUMPRODUCT((A1:A6DATE(2008,3,1))*(A1:A6<DATE(200 8,3,24))*(B1:B6))

Second formula:

=SUMPRODUCT((A1:A6TODAY()-10)*(A1:A6<TODAY())*(B1:B6))

If you want to include the start and end dates then change and < to
= and <=.


Change the ranges to suit your actual data.

Hope this helps.

Pete

On Apr 23, 4:06*pm, Jeremy wrote:
I need help with two formulas. *The first is I want a formula that would take
column A below and add the totals that are in column B that are between
3/1/08 and 3/24/08 in column A. *The total I would like in C1. * Also is
there a formula that can be written to take the date on the computer back 10
days and use column A to give a total in B that is in this date range?

Thank you for your help.

* * * * A * * * * * * * * B * * * * * * C * * * * * * *
1 * * * 3/23/08 * * * * 26.48
2 * * * 4/20/08 * * * * 0.00
3 * * * 4/16/08 * * * * 2.48
4 * * * 3/18/08 * * * * 46.81
5 * * * 3/10/08 * * * * 0.00
6 * * * 4/12/08 * * * * 42.00


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Sumif, SumProduct, CountIf Formula Help

Did you actually try those?

First one

=SUMPRODUCT(--($A$2:$A$200=--"2008-03-01"),--($A$2:$A$200<=--"2008-03-24"),$B$2:$B$200)

Jeremy, you state 10 days ago as one o=end of the range, what is the other.
I assumed today for this formula

=SUMPRODUCT(--($A$2:$A$200=TODAY()-10),--($A$2:$A$200<=TODAY()),$B$2:$B$200)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Joel" wrote in message
...
=SUMPRODUCT(IF(AND(A1:A6=DATEVALUE("3/1/2008"),A1:A6<=DATEVALUE("3/24/2008")),1,0)*B1:B6)

To get 10 days earlier subtract 10 from the dates

=SUMPRODUCT(IF(AND(A1:A6=(DATEVALUE("3/1/2008")-10),A1:A6<=(DATEVALUE("3/24/2008")-10),1,0)*B1:B6)


"Jeremy" wrote:

I need help with two formulas. The first is I want a formula that would
take
column A below and add the totals that are in column B that are between
3/1/08 and 3/24/08 in column A. The total I would like in C1. Also is
there a formula that can be written to take the date on the computer back
10
days and use column A to give a total in B that is in this date range?

Thank you for your help.


A B C
1 3/23/08 26.48
2 4/20/08 0.00
3 4/16/08 2.48
4 3/18/08 46.81
5 3/10/08 0.00
6 4/12/08 42.00



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF? SUMIF? SUMPRODUCT? IF? omss Excel Worksheet Functions 2 February 6th 08 12:22 AM
sumif, countif, sumproduct????? m fleming Excel Worksheet Functions 2 June 17th 06 03:11 AM
Sumproduct, If, Sumif, Countif, Match?? Herman56 Excel Discussion (Misc queries) 0 March 30th 06 01:40 PM
countif, sumif, sumproduct - I dont know which to use Jim Excel Worksheet Functions 3 January 4th 06 02:51 PM
Which formula to use? countif, sumif, sumproduct zubee Excel Discussion (Misc queries) 3 September 2nd 05 08:16 PM


All times are GMT +1. The time now is 11:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"