SUMIF Discrepencies

[brulottej]

Rather than ripping my hair out, I decided to come ask some experts.


--->LINK TO WORKBOOK: https://www.dropbox.com/s/47dcvmm2oq...ults.xlsx?dl=0

I have a base dataset consisting of 15584 lines of online sales data.

I am having to build a Sales by Product report and I am using the SUMIF function to pull the information I need.

The top of my report seems to be functioning correctly using the formula: =SUMIF(Data!B2:B15584, "STL-*",Data!L2:L15584), which should SUM the data in the L2:L15584 range IF the CRITERIA meets "STL-*" found in the B2:B15584 range. (A Text value, and I understand that may be an issue using this function) And yields the expected result.

Slightly further on the page, I have a section labeled SALE BY BRAND/ITEM CATEGORY, most of this section seems to function properly except when I start utilizing this SUM criteria in this equation: =SUMIF(Data!B2:B15584, "STL-AIR-*",Data!L2:L15584) on each category to get the Category Specific Totals.

This issue the SUM of the categories that exists under the STL brand does not equal the DataSet total for the STL Brand as a whole. Something is being left out somewhere or I am having a major issue using TEXT as my criteria in the SUMIF() function.

Should I go about the as a =SUM(IF()) array situation due to my queuing off of a TEXT formatted criteria rather than a number? If so, how would you recommend this?

Thanks for any assistance in advance. The fresh eyes on this is appreciated.

J

[loginjmor]

Hi -

It's very hard to tell from an image what is going on. If I were to guess, there may be something in your text field that isn't EXACTLY what you're searching for. Would it be possible to upload a sample of you spreadsheet with any sensitive data removed and a couple examples of the problem you're describing?

[brulottej]

Thanks for the response and your willingness to assist.

Click OPEN in the upper right on dropbox and you can download the Excel file and open it.

This IS sample data. Nothing Sensitive here.

That's what I was thinking too, but I have raked through the hundreds of lines for that category and still don't see how I am missing my match.

J

[mikeTRON]

Everything you are trying to do would be MUCH easier natively in a pivot table, so you dont MISS anything.
Are you familiar with pivot tables?
I am currently trying to find your issue, but a pivot automatically catches everything, and buckets them, without you missing anything.

EDIT: FOUND THE ISSUE.
You have a 402 records that should be "TOQ" but are instead "TOQ " which is causing your SUMIF to not capture it.


See the attached pivot table I made which took 15 seconds to build, compared to your sumifs



All I did was create a column that allowed me to slice the data you wanted.
In the DATA worksheet, you can alter my formula to remove the " " to make it a tidy report.

Subcategory formula now in Cell O2:

Please Login or Register  to view this content.

Subcategory formula to correct for the space:

Please Login or Register  to view this content.

Again, I HIGHLY recommend using a pivot table for your reports instead of an arbitrary list of sumifs for this exact reason.

[brulottej]

I understand that the pivot table is convenient and such, but it's not what is being asked of me at the moment, thanks for the recommendation though.

You're a life saver...figures it was in the sample DATA that was given to me.

Thanks Mike.

[mikeTRON]

Well tell whoever is asking for you to do something in a suboptimal way that you will do it in a more automated manner which is less error prone and easier to manage.
It's not that it is [only] CONVENIENT, it is also ACCURATE.

Also, I used the pivot to FIND the issue in a minute or so which proves it is the ideal way to handle this problem.

I am just a huge fan of making things as simple as possible, and efficient as possible. For example I dont usually use sumifs anymore unless it is on a few cells, but on what you built I would pivot in the background then index or vlookup off that aggregated data, because a vlookup is quick, and a SUMIFS is much more computationally intensive, and when you get 16,000 SUMIFS things tend to slow down hahah ask me how I know...

Glad I could help.