SUMIF missing one row out of calculations

[newbjohny]

Hi

I am trying to add up all the instances a certain person makes a sale in a spreadsheet. I have a column (B) with all the names, and a column (E) with all the sale amounts. Cell R2 holds the names in a table format and the SUMIF formula is in T2 - =SUMIF(B:B,R2,E2:E201)

At the bottom of my little summary table I've created I use a standard SUM formula to add up all the subtotals. Similarly, at the bottom of my large table I have a SUM formula adding up all the sale amounts. When comparing the 2 totals they do not match, they are out by the exact same amount of the sale amount in cell E2 each time. If I sort/filter the large table, and the value of E2 changes, the totals are always apart by the value of E2.

I've checked that all the names are typed exactly the same in the column, I've done a COUNTIF formula and the numbers for each name are correct. I've made sure the cells are formatted correctly for currency.

Can anyone shed any light on this for me please?

Cheers

J

[JohnTopley]

Your SUMIF will include the SUM totals if I understand your description correctly (as it looks at the total column).

If not post a file.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

I would try:

Please Login or Register  to view this content.

[Phuocam]

=SUMIF(B:B,R2,E2:E201)

Change to: =SUMIF(B:B,R2,E:E)

or: =SUMIF(B2:B201,R2,E2:E201)

[TMS]

I should have said, it is normal to have matching ranges and I think you are offsetting the comparison somehow.

[newbjohny]

Thanks all, that's fixed it I changed the B:B to B2:B201 and the totals now add up Thank you so much for your help, I've been scratching my head on this one for hours. haha. Thanks again

[TMS]

You're welcome. Thanks for the rep.