SumIf with a variable range

Question

Here is a simplified version of my situation.  I have a spreadsheet that shows revenue per month for a number of projects.  On a separate sheet I have two columns labeled "Rev to Date" and "Rev to Completion".  So if it were the end of May all the revenues from Jan-May would be in the first column and Jun-Dec in the second column.

Ordinarily I would use a SUMIF function and above the data have a row with 1 or 0 in the cells.  And the formula might be:  SUMIF(C1:M1,1,C25:M25).  All complete months would have the 1 and thus be added.  My problem is I don't always know which project I am summing so the sum range - in this case C25:M25 - changes.

How can I make that formula dynamic so it looks for what range to sum and then applies the SUMIF?  I hope this isn't too confusing.  If anyone has any ideas I would be grateful to hear them.

Andrew

Answer 1

Hi,

Try this

1. Data in sheet 1 is arranged as follows: - project names from cell A2 downwards (say in A2:A4), months (Jan, Feb, Mar etc) are in B1:M1.  Figures are in B2:M4
2. On sheet, project names are mentioned in A2:A4
3. In cell B2, enter this formula and copy down to compute Revenue to date

=SUM(INDEX(Sheet1!$B$2:$B$4,MATCH(A2,Sheet1!$A$2:$A$4,0),1):INDEX(Sheet1!$B$2:$M$4,MATCH(A2,Sheet1!$A$2:$A$4,0),MATCH(TEXT(TODAY(),"mmm"),Sheet1!$B$1:$M$1,0)))

4. In cell C2, enter this formula and copy down to compute Revenue to completion

=SUMPRODUCT((Sheet1!$A$2:$A$4=A$2)*(Sheet1!$B$2:$M$4))-B2

Hope this helps.

Answer 2

Revenue in the past cannot change.  But future revenue is only a forecast so, yes, it can change.  Plus the number of months being added to one column vs the other would change as the months go by.

Data is laid out like your second example.

Answer 3

Bernard,

Yes, that is exactly right.  On the first page with the source data I have, to the left, a column that concatenates the name of the project and whether the line is Revenue or Expense.  On the second page I have a list of each of the projects in Col A and then columns for Rev to Date and Rev to Completion in Column B:C

So theoretically I ought to be able to do something like a VLOOKUP since I have a common lookup value on each sheet.  I just don't know how to combine that with a SUMIF.

Andrew

Answer 4

So sometime you will sum C25:M25 and another time pehaps C33:M33?

Is there any data that tells you which row a given project lives in?

Answer 5

Here is a simplified version of my situation.  I have a spreadsheet that shows revenue per month for a number of projects. 

Does the Revenue per month change every month? or is it static

Date          Project1       Project2

Jan $1000    $3000

Feb           $1500           $3200

or

Project     Rev per month

Project1    $1000

Project2     $3000