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

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

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