• 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.

Single formula to sum items in lists of varying lengths according to criteria

B

BobHoskins

New Member
Hi Guys,

The problem is hard to summarise in the title so I apologise if it's a little confusing (if anyone can think of a better title after understanding the problem, please let me know and I'll change it!). Please take a look at the attached file.

Basically, I need a single formula (i.e. no vba or helper columns) to sum the number of Worms and Beer for Wednesday for people with (JAM) in parentheses after their names only.

There must be a way to do this, does anyone have any thoughts?

"Objective: ONE formula in ONE cell, with no helper columns, and no VBA, to give me the sum of the worms and beer on Wednesday for people with JAM in parentheses only.


•The number of columns will always be the same
•Names will always be in column A and item list will always be in column B
•The item list can be any number of rows
•The item list can include any items, but will always be text strings and every list will always include beer and worms
•I've only put three name s here just to simplify my problem, in reality there will be many more

My thinking:
•Relatively simple array formula to find row number where right(A:A,5)="(JAM)"
•Use these row numbers and either indirect or offset to get the starting cell of the item list
•Use array formula to generate array the exact size of the item list
•Use array formula to get row numbers of instances of worms and beer
•Use index to get intersect of above calculated row and column E.

The closest I've gotten is in cell J8, I've managed to get to get to point three in 'my thinking' but only for the first case, hence the min (I was trying to get it to work for the first instance before I tried it on the whole dataset)."

Please ignore formula length, volatile functions, calculation speeds, and any other of the usual worries of formula construction, I'd just like to know if it's possible!

If there are any questions then please let me know, and if this isn't an appropriate thread then let me know and I'll delete.

Many thanks
 

Attachments

  • Excel Problem.xlsx
    12.7 KB · Views: 14
If I use helper columns then yes, the problem becomes relatively simple. But given that I can generate an array of the item list for the first instance without using them (and without repeating any names), I think it should be possible to do the whole thing without using helper columns. That said, the ninjas here know much more about Excel than I do, hence this thread.

If the consensus is that it cannot be done given the constraints then that's fine, but I'll wait for more people to chime in before I throw in the towel :)
 
It is not"consensus" but my humble opinion.

Any one else can surely do that without helper column. As i was reviewing the worksheet it appeared that either some very tricky formula or a very lengthy one will solve the issue. But as i have observed in course of time, both become problematic when they are updated for ranges or when you try to understand them. :D
 
Hi ,

See if this works :

=SUMPRODUCT(IFERROR(ISNUMBER(SEARCH("(JAM)",T(OFFSET($A$1,-1+LOOKUP(ROW($A$1:$A$20),IF($A$1:$A$20<>"",ROW($A$1:$A$20))),)))) * ($B$1:$B$20 = {"Worms","Beer"}) * INDEX($C$1:$I$20,,MATCH("Wednesday",$C$1:$I$1,0)),0))

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 
Wow, that is absolutely amazing, thanks Narayan! I shouldn't be surprised given the masters who post here but even so.

For a mere novice such as myself, would you mind explaining the thought process behind the formula?

I understand what all of the functions do individually but it's hard to understand how everything fits together. The only thing that is obvious to me and doesn't need explaining is multiplying by the index-array at the end.

Even if you don't reply, thank you very much!
 
Hi ,

The components of the formula are as follows :

1. LOOKUP(ROW($A$1:$A$20),IF($A$1:$A$20<>"",ROW($A$1:$A$20)))

This is a standard technique for returning an array of values , which fills in the blanks by repeating the previous entries.

In your data , the rows which are not blank are rows 1 , 8 and 14 ; thus , what the above segment will do is fill in the blanks from rows 2 through 7 with the number 1 , the blanks from rows 9 through 13 with the number 8 , and the blanks from rows 15 through 20 with the number 14. The result will be the array :

{1;1;1;1;1;1;1;8;8;8;8;8;8;14;14;14;14;14;14;14}

2. We now subtract 1 from the above values , because the OFFSET function needs to start with an offset of 0 to get the first value.

3. Using the above array with the OFFSET function will give us the array :

{"Mr A (JAM)";"Mr A (JAM)";"Mr A (JAM)";"Mr A (JAM)";"Mr A (JAM)";"Mr A (JAM)";"Mr A (JAM)";"Mr B (EAR)";"Mr B (EAR)";"Mr B (EAR)";"Mr B (EAR)";"Mr B (EAR)";"Mr B (EAR)";"Mr C (JAM)";"Mr C (JAM)";"Mr C (JAM)";"Mr C (JAM)";"Mr C (JAM)";"Mr C (JAM)";"Mr C (JAM)"}

4. Since the OFFSET formula returns the #VALUE! error values , wrapping it inside a T function returns the actual text values.

5. Now we can search for the sub-string (JAM) in this array ; wrapping the ISNUMBER function outside the SEARCH function is a standard way to return TRUE / FALSE values.

This is because the SEARCH function returns two different type of results ; if the searched for value is found , it will return an integer value signifying the location within the cell contents where the searched for sub-string is found ; if not , it returns the #VALUE! error value. The ISNUMBER function will return TRUE in the former case and FALSE in the latter.

6. ($B$1:$B$20 = {"Worms","Beer"})

This returns an array of values which is TRUE if the cell contains either Worms or Beer , and FALSE if it contains anything else.

Narayan
 
Thank you so much for taking the time to explain that to me, it's helped my Excel knowledge immensely. I'm already thinking of uses for this new knowledge!
 
You must log in or register to reply here.
Back
Top