Sumif with variable target

  • Thread starter Thread starter Brisbane Rob
  • Start date Start date
B

Brisbane Rob

Column A has over a hundred different parts codes. The order of codes i
this can change from month to month.

Column C is a list of all the parts codes from all invoices in Janaur
with their sales value in Column D. These columns are a couple o
thousand lines long. Columns E through Z contain the rest of the month
of the year, with a list of parts codes off the invoices in th
respective months and their values.

If I want to find out the totals for the codes in say January I use
sumif formula in column B. But if I want to find out the totals fo
March I have to re-wrtie the formula. Is it possible to have th
formula look up the values from a month declared in cell A1?

Thanks for any ideas
 
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 a
required, e.g. mmmm) then you could use this formula in B2 copied dow
column

=SUMIF(INDEX(C$2:Y$2000,0,MONTH(A$1)*2-1),A2,INDEX(D$2:Z$2,MONTH(A$1)*2-1)
 
Back
Top