Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cumulative totals
I have a spreadsheet as follows:
A B C date MD902 40 date MD902 30 date BO105 20 date BO105 10 and so on between rows 6 to 20006 I would like to be able to produce the total of column 'C' for the last 6 month of MD902 and BO105. -- AOU |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cumulative totals
Try this,
=SUMPRODUCT((A1:A100=DATE(YEAR(TODAY()), MONTH(TODAY())-6, DAY(TODAY())))*(B1:B100="MD902")*(C1:C100)) Change A1:A100 etc to match your range You might also consider referring to a cell for MD902 instead of having the search string within the formula. Mike "AOU" wrote: I have a spreadsheet as follows: A B C date MD902 40 date MD902 30 date BO105 20 date BO105 10 and so on between rows 6 to 20006 I would like to be able to produce the total of column 'C' for the last 6 month of MD902 and BO105. -- AOU |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cumulative totals
try:
=SUMPRODUCT(--(MONTH($A$1:$A$50)=2)*($B$1:$B$50={"MD902","BO105 "})*($C$1:$C$50)) assuming last 6 months is data from February onwards and there is only one year. Or using 183 days as 6 months =SUMPRODUCT(--($A$1:$A$50=TODAY()-183)*($B$1:$B$50={"MD902","BO105"})*($C$1:$C$50)) "AOU" wrote: I have a spreadsheet as follows: A B C date MD902 40 date MD902 30 date BO105 20 date BO105 10 and so on between rows 6 to 20006 I would like to be able to produce the total of column 'C' for the last 6 month of MD902 and BO105. -- AOU |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
cumulative totals
Thanks very much, it works fine. Just to add to this question.....How would I
use this formular to fine out 3 days and 7 days as well. Thanks very miuch -- AOU "Mike H" wrote: Try this, =SUMPRODUCT((A1:A100=DATE(YEAR(TODAY()), MONTH(TODAY())-6, DAY(TODAY())))*(B1:B100="MD902")*(C1:C100)) Change A1:A100 etc to match your range You might also consider referring to a cell for MD902 instead of having the search string within the formula. Mike "AOU" wrote: I have a spreadsheet as follows: A B C date MD902 40 date MD902 30 date BO105 20 date BO105 10 and so on between rows 6 to 20006 I would like to be able to produce the total of column 'C' for the last 6 month of MD902 and BO105. -- AOU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CUMULATIVE TOTALS | Excel Discussion (Misc queries) | |||
Pivot Table groups & cumulative totals | Excel Discussion (Misc queries) | |||
Charting: Displaying cumulative totals at top of stacked columns | Charts and Charting in Excel | |||
cumulative totals in a workbook | Excel Worksheet Functions | |||
How to make cumulative totals appear on a graph | Excel Discussion (Misc queries) |