Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditionally SUM Across Multiple Columns
I am quite certain that SUMPRODUCT is the solution to my problem, but
I cannot get it to work correctly. My 'Data Source' sheet looks like this: A B C D 1 Account No. 5/2007 6/2007 7/2007 2 001 100.00 200.00 300.00 3 002 50.00 60.00 70.00 4 003 10.00 11.00 12.00 The sheet ('Analysis') that I am performing my analysis on will look like this: A B C 1 Account No. Last 3 Months Last 2 Months....... 2 001 600.00 500.00 3 002 180.00 130.00 4 003 33.00 23.00 For the purpose of my question, let's focus on getting the formula for cell B2 on the 'Analysis' sheet. I suspect the formula will work like this: =SUMPRODUCT((condition that matches account number on Analysis to Data Source)*(condition that selects which months to include)*(some range of cells)) Any help would be very much appreciated. Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditionally SUM Across Multiple Columns
in b2 in "Analysis":
=SUMPRODUCT((Sheet1!$A$2:$A$4=$A2)*(MONTH(Sheet1!$ B$1:$D$1)=MONTH(TODAY())-3)*(Sheet1!$B$2:$D$4)) in C2: =SUMPRODUCT((Sheet1!$A$2:$A$4=$A2)*(MONTH(Sheet1!$ B$1:$D$1)=MONTH(TODAY())-2)*(Sheet1!$B$2:$D$4)) Copy both down Sheet1 is you "Data Source" HTH "DoooWhat" wrote: I am quite certain that SUMPRODUCT is the solution to my problem, but I cannot get it to work correctly. My 'Data Source' sheet looks like this: A B C D 1 Account No. 5/2007 6/2007 7/2007 2 001 100.00 200.00 300.00 3 002 50.00 60.00 70.00 4 003 10.00 11.00 12.00 The sheet ('Analysis') that I am performing my analysis on will look like this: A B C 1 Account No. Last 3 Months Last 2 Months....... 2 001 600.00 500.00 3 002 180.00 130.00 4 003 33.00 23.00 For the purpose of my question, let's focus on getting the formula for cell B2 on the 'Analysis' sheet. I suspect the formula will work like this: =SUMPRODUCT((condition that matches account number on Analysis to Data Source)*(condition that selects which months to include)*(some range of cells)) Any help would be very much appreciated. Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditionally SUM Across Multiple Columns
Ah, that worked. Thanks so much for your help!
Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple sets of columns into one set of columns no blanks | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Macro preparing numbers to be booked (columns to be chosen conditionally) | Excel Worksheet Functions | |||
count conditionally across two columns? | Excel Worksheet Functions |