Sumif = #VALUE error

**Dial1** · 08-02-2012, 05:57 PM

I am using a sumif formula that is using offset derived ranges. Formula example below.

=SUMIF('Prev Mth 2012'!$C:$C,$B8,OFFSET('Prev Mth 2012'!$R$1,,'LC PG'!BO$3):OFFSET('Prev Mth 2012'!$R$1,,BO$3))/1000

On a few cells I am getting a #VALUE error. It only happens in every 4th column and not in all the cells that same formula is in?

Couple questions.
1) Why am I getting this error?
2) In the second offset formula, which sets the end of the range reference, should my reference cell ( ie $R$1) actuall be $R$50 if I am looking to generate a sum range from row 1 to 50?

Thanks for your help.

**daddylonglegs** · 08-02-2012, 06:19 PM

I'm not sure this will work at all in the way you want. Consider this formula that's similar to yours in that the first range is a whole column and the second range is a smaller range

=SUMIF(C:C,"x",B10:B50)

What does that do? If C1 = "x" the value in B10 will be added to the total, if C2 = "x" the value in B11 will be added to the total etc. that is it sums values on rows other than the rows where the criterion matches......also the sum range used is always as long as the criteria range, so in that formula if C1000 is = "x" B1009 will be added to the total even though that cell is not within the range B10:B50 listed.

I think you need to use OFFSET to set the first range too, can you explain the logic, what's in BO3?

---------- Post added at 11:19 PM ---------- Previous post was at 11:11 PM ----------

...actually having posted that reply I think I now realise what you are doing....If you are trying to use a SUMIF on rows 1 to 50 only then you should set that in the first range as I said, i.e. try

=SUMIF('Prev Mth 2012'!$C$1:$C$50,$B8,OFFSET('Prev Mth 2012'!$R$1:$R$50,,'LC PG'!BO$3))/1000

**Dial1** · 08-02-2012, 06:42 PM

Thanks for the reply daddy. I conpletely get what you are saying and that is how I had the formula set up first.

=SUMIF('Prev Mth 2013'!$C1:$C5000,$B8,OFFSET('Prev Mth 2013'!$R$1,,'LC PG'!BG$3):OFFSET('Prev Mth 2013'!$R$5000,,BG$3))/1000

I had originally gotten the error with this version so I changed it to the formula posted in the first post. However, I know randomly ( not in all cells) get the #value error again.

FYI references as follows:
C1 & C5000 are my offset reference cells to set my range between rows
B8 - is the sum reference criteria
R1 & R50 again set the row range for the column to be summed
BO3 - is my column offset value ( number of columns to offset)

**daddylonglegs** · 08-02-2012, 07:20 PM

That formula looks like an improvement, although you can use this version to shorten

=SUMIF('Prev Mth 2013'!$C1:$C5000,$B8,OFFSET('Prev Mth 2013'!$R$1$R$5000,,'LC PG'!BG$3))/1000

can't see how you would get #VALUE! errors with either, though, unless you have #VALUE! errors in the data (sum range), is that possible?

In which sheet is the formula? I note that in one OFFSET function you use BG$3 and in the other 'LC PG'!BG$3, is that the same value, is the formula in 'LC PG' sheet?

**Dial1** · 08-03-2012, 03:29 PM

Thanks for the help Daddy. I figured it out imediately today when I retured to this spreadsheet. (working too long on the same thing clouds the mind) It was exactly as you were curious about the difference between BG$3 and the 'LC PG BG$3. The pages I was getting the error on were non LC PG tabs so there was an incorrect reference from a column shift. Thanks for helping me realize my error!