**Need SumIf with Variable Sum Range**

[vanray]

I need to take the dollar amounts from Sheet 1 and get them into the format in Sheet 2. I already have the first two columns in Sheet 2 populated with the department and account, so I just need to use some lookup function to grab the dollar amounts. I've created a unique identifier in Sheet1 Column C by concatenating the month and account.

I'll be writing the formula in Sheet 2!C2. In English, I want the formula to say:

sumif(Sheet1!C:C,"JanTaxes","only in column D")

I have used nested vlookups before where I referenced a table that I had created associating a department with a column number, but in this case it would only return the first occurence. I can use the sumif to return all occurences, but can't use a variable sum column.

Any insight would be GREATLY appreciated.

Thank you!


Sheet1:
--A------------B-----------C------------D--------E-----------F
Month____Account___Concat_____Finance___IT_____Marketing
Jan--------Taxes-------JanTaxes-------$10-------$0---------$0
Jan--------Rent--------JanRent--------$25--------$0---------$0
Jan--------Software----JanSoftware----$0--------$50--------$0
Jan--------Taxes-------JanTaxes-------$75-------$0---------$0

Sheet2:

-A-----------B----------C---------D----------E
Dept____Account____Jan_____Feb______Mar
Finance---Taxes-------$85
Finance---Taxes-------$25
Finance---Software----$0
IT--------Rent--------$0
IT--------Taxes-------$0
IT--------Software----$50
Marketing-Rent--------$0
Marketing-Taxes-------$0
Marketing-Software----$0

 

[Airfix9]

Which version of Excel are you using?

 

[vanray]

Excel 2007

 

[Airfix9]

=SUMIF(Sheet1!$C:$C,C$1&$B2,INDEX(Sheet1!$1:$1,1,MATCH($A2,Sheet1!$1:$1)))

Adjust to fit your ranges.

 

[vanray]

Thank you for the quick reply. I'm almost there, but I don't quite understand the "Sheet1!$1:$1" parts. Could you please describe what is happening in the index and match functions? Also, does "Sheet1!$1:$1" reference the first column of the sheet or are the 1s just place holders? Thank you for your help. I greatly appreciate it.

 

[vanray]

After further review, I have figured it out. Thank you so much for your help!