Problem using sumif to divide by a range

**Flobiwan** · 09-26-2012, 10:18 AM

I am trying to use sumif to divide by a range and keep getting an error message. Basically, i have a row of data in cell "I" and categories listed down row "C". I am trying to create a formula at the bottom of the spreadsheet that says, "for all the rows in the top of the sheet where the word in column C matches the word in column C on this row, divide X by the total".

the problem seems to have to do with dividing by a range. Here is what i am trying to use:

=SUMIF($C$12:$C$25,$C36,(I36/(I12:I25)))

Excel keeps telling me there is an error with the formula. Can someone tell me what i'm doing wrong?

**NBVC** · 09-26-2012, 10:25 AM

Try instead:

=SUMPRODUCT(--($C$12:$C$25=$C36),I36/(I12:I25))

or if you have blanks in I12:I25...

=SUMPRODUCT(--($C$12:$C$25=$C36),I36/(I12:I25+1E-99))

**Flobiwan** · 09-26-2012, 11:13 AM

the first formula gave me a div/0 error. The second one almost works. When i copy it down to calculate the 5 categories in 5 different cells at the bottom, the formula seems to work fine except for one cell where, instead of calculating the correct value of 0.0765, it comes up with a value of 5.53. Bizarre. I double-checked the references and everything looks fine. it's just that cell that calculates oddly.

**NBVC** · 09-26-2012, 11:36 AM

I did neglect to make the second range absolute.... is that it?

=SUMPRODUCT(--($C$12:$C$25=$C36),$I36/($I$12:$I$25+1E-99))

**Flobiwan** · 09-26-2012, 01:15 PM

No, i actually made that change before but it doesnt make a difference. It's weird, the way the formula is, it should be taking a group of numbers that add up to 33,603.46 and then dividing cell I36 which is 2570.66 by the 33,603.46 to return a value of 0.0765. However, it returns a value of 5.53 which, if i work backwards, means that instead of totaling the numbers at 33,603.46, it's adding them up to be 464.00!! And the smallest number in that group is 1,342! It's especially wierd since it works correctly in all the other cells in that column. here's the formula exactly as it's entered:

=SUMPRODUCT(--($C$12:$C$25=$C36),I$36/(I$12:I$25+1E-99))

Any ideas?

**NBVC** · 09-26-2012, 01:48 PM

Can you post a sample workbook showing this.... and is it Excel 2003 you are working with?

**Flobiwan** · 09-26-2012, 02:41 PM

Here's a sample of the spreadsheet. It is Excel 2003.

**NBVC** · 09-26-2012, 04:20 PM

Place this formula in R33:

=IF(H12="","",$H$36/H12)

copied down

This is essentially taking the H36 value and dividing by each value in H12:H25 just as this part of my formula is doing: H36/(H$12:H$25+1E-99) only that I added the +1E-99 to avoid the #Div/0 error where blanks occur in H12:H25.

Now in I36, if you replace the existing formula with: =SUMPRODUCT(--($B$12:$B$25=$B36),R33:R46) where now the R33:R46 is being summed you get the same result... notice in the corresponding values to your B36 (AmexTravel) are in R35:R41.... so the sum seems correct for the logic...