Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with variable target
Column A has over a hundred different parts codes. The order of codes in this can change from month to month. Column C is a list of all the parts codes from all invoices in Janaury with their sales value in Column D. These columns are a couple of thousand lines long. Columns E through Z contain the rest of the months of the year, with a list of parts codes off the invoices in the respective months and their values. If I want to find out the totals for the codes in say January I use a sumif formula in column B. But if I want to find out the totals for March I have to re-wrtie the formula. Is it possible to have the formula look up the values from a month declared in cell A1? Thanks for any ideas. -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096 View this thread: http://www.excelforum.com/showthread...hreadid=525440 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with variable target
Presumably you have a formula currently in B2 something like =SUMIF(C$2:C$2000,A2,D$2:D$2000) assuming you have a date in A1 (i.e. for March 01/03/2006, format as required, e.g. mmmm) then you could use this formula in B2 copied down column =SUMIF(INDEX(C$2:Y$2000,0,MONTH(A$1)*2-1),A2,INDEX(D$2:Z$2,MONTH(A$1)*2-1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525440 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with variable target
Thanks a lot - your help's appreciated -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096 View this thread: http://www.excelforum.com/showthread...hreadid=525440 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
Return Numerical Label for LAST value Subtracted to reach Sum Target Value | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions |