• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIF String in Range - can someone explain how this works?

S

staregirl

New Member
Hello!

Bit of an odd question - I have a formula that works, but I want to know HOW it works?

It's someone else's spreadsheet, so I'll suggest better methods to them next month, but for now:

This formula is in a sheet that's totalling order details pulled together from other tables.

=SUMIF(H:H,"*"&Licence_Totals[@[License (Auto-Populated from Table 3)]]&"*",Data!F:F)

Column H contains item names that are not unique, and column F contains the number of those items ordered.

The formula is checking a list of unique item names (or categories, I suppose), finding all instances of each string in column H and totaling the number of each item ordered.

So first list might be:

Bananas - 1
Apples - 2
Green Bananas - 2
Apples - 1
Bananas - 3

And it will return:

Bananas - 6
Strawberries - 0
Apples - 3

Where Bananas, Strawberries, Apples is a fixed list (the zero orders are also important).

Why I'm confused:

As far as I can tell, it should be comparing strings in column H with strings in the Licence column of the Licence_Totals table.

However, if I move the Licence_Totals table down by inserting rows above it, the formula goes out of whack by the same number of rows (so it returns first total to cell L6, instead of L4):

Bananas - 0
Strawberries - 0
Apples - 6
Blank
Blank - 3

I can fix it by moving the destination table down an equivalent number of rows.

That makes me think that instead of checking WHAT it is (the string) it's pulling the data based on WHERE it is, and I can't see why? What don't I understand?

Thanks!
 
=SUMIF(H:H,"*"&Licence_Totals[@[License (Auto-Populated from Table 3)]]&"*",Data!F:F)
at first glance...
So the full column F is referenced to do the sum: why not using the table structured reference? Say the table is named "tData" and the column is "Licence" => SUMIF(H:H,"*"&Licence_Totals[@[License (Auto-Populated from Table 3)]]&"*",tData[Licence]).
Same goes for reference the full column H by the way.
 
Hi - have checked with the owner, workbook attached.

I haven't got around to defining names for everything yet, but I will - this has been woven from the ashes of an existing spreadsheet..

Formula is in column L of the Data sheet.

Process - Project plan submitted. The plan will detail how many of each type of Item needs to be set up.

Item type and quantity of each are input to the cover sheet.

Each Item is a basket of products (licences), they are in the second table of the (will be hidden) Data sheet.

These feed back to the LAP sheet which summarises the total licence orders for this project, there's a macro that only shows licence types with a positive Quantity.

To see why it's like that, the licence Scalable Asset Vision (Server) is a component of 5 'baskets' which might all be selected in the same project, so are summarised in one row on the LAP sheet.

I just want to know why moving the table on th LAP sheet makes the formula go wonky. It's not a big deal, but I'd like to know :)

Thanks!
 

Attachments

  • Template - LAP - example.xlsm
    47.2 KB · Views: 7
on sheet LAP: Licence Quantity Total formula in column C refers to DATA sheet column L
on sheet data column L: contains the formula mentioned above and refers to column F on the same sheet. Whereas the table referenced here Licence_Totals[@[License (Auto-Populated from Table 3)]] is on the sheet LAP. These formulas refer to each other?!
Column F: is a vlookup, with the lookup key referring to the full column, from yet another table Std_Project_Item_List.
That table is on the sheet "cover sheet". Column G contains the quantity.

Each of the formulas use full column references where it is not needed. If you move around the tables, the relative positions in those references change and you end up with "wonky" results. It is working per consequence because both tables start on the same row. Not because of a solid formula.
Sorry to say, but the formulas aren't the best I've seen.
 
Was working in the same direction... But too busy explaining, not solving and got confused by the "circular column reference". In the end beaten by the ninja:).
 
Hi both

Don't get me wrong, I really appreciate your input, but I wasn't asking for help to fix the workbook. Like I said, it's someone else's thing, built from another thing, and I know it's a bodge job.

I was simply wondering WHY the existing formula is behaving how it is, because it did something I wasn't expecting.

I have already said to the owner we should start afresh now he's finally worked out what it's meant to be. He's a novice but I shall pass on your comments about the quality of his attempt!
 
You must log in or register to reply here.
Back
Top