SUMIFS and Vlookup

[TheEnergyMan]

I cant seem to get my head around SUMIFS and VLOOKUP,

Im currently using:

=SUMIF('Gas July'!$B$2:$D$217, VLOOKUP($A3, 'Gas July'!$B$2:$D$217, 1, FALSE), 'Gas July'!$D$2:$D$217)

Which points to a sheet with only July Data and multiple sites and it gives me the number I want (total usage for a particular building), however to save me having multiple tabs I have a main tab with all my years data on it for multiple sites.

I got to:

SUMIF(Gas!A2:BA4644, VLOOKUP($A3,Gas!A2:BA4644, 53, FALSE),Gas!A2:BA4644)

Which Again gives me the total for a site but it gives me the total for all data relating to site "A3"

Ive tried:

=SUMIFs(Gas!BA2:BA4644, Gas!A2:BA4644, VLOOKUP($A3,Gas!A2:BA4644, 1, FALSE), Gas!A2:BA4644, VLOOKUP($B2, Gas!A2:BA4644, 1, FALSE))

Which makes sense to me, as the first Vlookup relates to the site "A3" and the second lookup relates to the month "B2" but all i get is errros and '#VALUE!"

in an attempt to better understand Sumifs i tried putting my sumif statement into a sumifs statement and changing the order of the Criteria, range and SumRange but that also throws out a "#VALUE!"

example:
SUMIF(Gas!A2:BA4644, VLOOKUP($A3,Gas!A2:BA4644, 53, FALSE),Gas!A2:BA4644)

SUMIFS(Gas!A2:BA4644, Gas!A2:BA4644, VLOOKUP($A3,Gas!A2:BA4644, 53, FALSE))

Although Ive moved the Criteria, Criteria Range and Sum range around accordingly it doesnt work?? What am I not understanding here?

Thanks

 

[DanteAmor]

You can give an example of your data and what are the criteria to sum.

 

[TheEnergyMan]

Of the sheet im pulling the data from, column A is the name of the site, Column C is the Date, column BA is the total for that day, and Column BB is the Month, format MMM- YY

 

[DanteAmor]

Assuming your data on the "gas" sheet is like this:

Gas
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >BA</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >BUILDING</td><td >DATE</td><td > </td><td >VALUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >build1</td><td style="text-align:right; ">15-ago</td><td > </td><td style="text-align:right; ">123</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >build2</td><td style="text-align:right; ">15-sep</td><td > </td><td style="text-align:right; ">133</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >build3</td><td style="text-align:right; ">15-oct</td><td > </td><td style="text-align:right; ">143</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >build1</td><td style="text-align:right; ">20-ago</td><td > </td><td style="text-align:right; ">153</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >build2</td><td style="text-align:right; ">20-sep</td><td > </td><td style="text-align:right; ">163</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >build3</td><td style="text-align:right; ">20-sep</td><td > </td><td style="text-align:right; ">173</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >build1</td><td style="text-align:right; ">16-oct</td><td > </td><td style="text-align:right; ">183</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >build2</td><td style="text-align:right; ">17-oct</td><td > </td><td style="text-align:right; ">193</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >build3</td><td style="text-align:right; ">18-oct</td><td > </td><td style="text-align:right; ">203</td></tr></table>



Try this

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >build2</td><td style="text-align:right; ">296</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B3</td><td >=SUMPRODUCT((Gas!A2:A10=A3)*(MONTH(Gas!B2:B10)=B2)*(Gas!BA2:BA10))</td></tr></table></td></tr></table>

 

[TheEnergyMan]

Hi DanteAmor, Thanks for all of your help on this matter, sadly that formula didnt work for me either, I got a NUM! error.


The good news here is that I was able to get it working with "=SUMIFS(Gas!$BA$2:$BA$4644, Gas!$A$2:$A$4644, $A3, Gas!$D$2:$D$4644, $B$2)"

I think I was missunderstanding the nature of Vlookup and that was what was messing up my formula.

Thanks again for all your help!

 

[DanteAmor]

Glad to know you found a solution.