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

Formula Challenge 021 - SUMIF in Merged cells.

H

Haseeb A

Active Member
Hello Smart 'Excel'ers,

Here is an Excel challenge for you all.

If we have data as following;


D----------V
A----------1
E----------2
<blank>--3
<blank>--4
<blank>--5
<blank>--6
<blank>--7
<blank>--8
B----------9
<blank>--10
2----------11
D----------12
A----------13
<blank>--14
<blank>--15
<blank>--16
<blank>--17
<blank>--18
F----------19
<blank>--20

As you can see <blank>-- cells merged, so non blank cell above that are its merged value. So the challenge is use SUMIF to get appropriate SUM.

Eg: If we look for A, the values are 1, 13, 14, 15, 16, 17, 18. So total should be 94

Note:

Range: You can use named range D
Sum_Range: You can use named range V

Rules:

1. No VBA only formulas.
2. Use only these 2 named ranges.
3. No Fill blank method, Press F5, special >> Blanks >>

Final answer should be;

A = 94
B = 19
2 = 11
D = 12
E = 35
F = 39

I am sorry, if it is an ugly layout posting. Any Ninjas are welcome to change its layout or more appropriate heading at any time.

See the attached file.

Haseeb.
 

Attachments

  • Formula Challenge - 021 - SUMIF in Merged Cells.xls
    34 KB · Views: 247
Hello Hui,

This is a challenge, not a question. I do have an answer. I am apologize, if something went wrong on this thread. I have edited some of the part, you or any other Ninjas always welcome for any other changes, if required.

Sorry for the inconvenience.
 
Thanx Haseeb,
It read as a question and hence my response
I will edit it slightly to make it clearer
 
Hi Haseeb,
The following is one approach... I am sure it can be optimized further, and will look at that in the morning.

=SUM(MMULT(N(LOOKUP(ROW(D),IF(D<>"",ROW(D)))=TRANSPOSE(IF(D="A",ROW(D)))), ROW(D)^0)*V)

entered with Ctrl + Shift + Enter.

-Sajan.
 
@ Hui, Thank you for the edit.

@ Sajan. Nice one. I think, if you close look on the formulas, you can still shorten ;) , which is my answer.

@ Jeff, waiting for your magic...
 
Sam, sorry for the confusion. Can use any function. Just used SUMIF for easy understanding.

Just to query. I see a line which says that the solution should be using SUMIF function
Click to expand...

=SUM((LOOKUP(ROW(D),IF(D<>"",ROW(D)))=TRANSPOSE(IF(D=2,ROW(D))))*V)
Click to expand...

Good solution...!
 
I think we can drop IF from Lori's original formula. Following works:
Code: 
=SUM((LOOKUP(ROW(D),ROW(D)/(D<>""),D)=D5)*V)
[CTRL+SHIFT+ENTER]
or it can be SUMPRODUCTed for non-array entry:
Code: 
=SUMPRODUCT((LOOKUP(ROW(D),ROW(D)/(D<>""),D)=D5)*V)
 
10 days have been passed. Just want to say 'Thank you' for every one for your contributions.

Keep 'Excel'ling
 
Hi Haseeb,
It was an interesting challenge! Hope you post some new ones soon!

Regards,
Sajan.
 
Thanks Haseeb, this was a great challenge.
I have just posted a new one if anyone wants a go...

Regards, Lori
 
Awesome that you guys are keeping this series alive.
I've been too busy the last wee while to keep up with developments in this series, but am bookmarking this for future study when things quieten down.
 
Haseeb...I finally got a chance to look at this challenge and your formula. Just awesome, dude.

I didn't try to solve it myself...a bit busy at the moment.
 
Hi all,

{=SUM((INDEX(D;N(IF({1};MATCH(ROW(D);IF(NOT(ISBLANK(D));ROW(D);"");1))))="A")*V)}
{=SUMPRODUCT(--(INDEX(D;N(IF({1};MATCH(ROW(D);IF(NOT(ISBLANK(D));ROW(D);"");1))))="A");V)}

Notes:
- both are CSE formulae (or array formulae, entered with CTRL+SHIFT+ENTER)
- D and R must be finite/delimited ranges, you cannot use entire columns (like one can do with a classic SUMIFS formulae where Excel seems to be able to intersect input ranges with ActiveSheet.UsedRange to limit calculations) or Excel will attempt calculation til row 1048576!!!
- my solution is a bit nasty, but I initially wanted not to use LOOKUP (the above solution using LOOKUP is specifically based on how the function deals with #DIV/0! errors, and it is lucky that it works!) . I ended up using an even more obscure trick with the N(IF({1};SOME_RANGE) part in INDEX(D;N(IF({1};SOME_RANGE) to "dereference" the 2nd argument of the INDEX function (noted as SOME_RANGE to simplify) and force it to return an array in the array formula
- CSE formula is also required for the solution using the SUMPRODUCT function because of the IF function which systematically requires CSE validation when working with ranges
 
Since this thread has been resurrected …
Now one can use
= SUM( FILTER( V, IF(V, LOOKUP(V, V/(D<>""), D) ) = "A") )
with no CSE,
[If V is not a monotonic increasing sequence then ROW or SEQUENCE can be used to generate a number sequence for use within the LOOKUP]
 
Things are still changing though, in this instance, modern methods are not necessarily better than LOOKUP. That performed so well as a 'fill down' mechanism.

Last year I might have gone for
Code: 
= LET(
    group,    UNIQUE(SORT(TOCOL(D,1))),
    filldown, SCAN(, D, LAMBDA(a,d, IF(LEN(d), d, a))),
    total,    MAP(group, LAMBDA(g, SUM(IF(filldown=g, V)))),
    HSTACK(group, total)
  )
to build the entire result table as a single dynamic array.

This year one could also use GROUPBY.
Code: 
= LET(
    filldown, SCAN( , D, LAMBDA(a,d, IF(LEN(d), d, a))),
    GROUPBY(filldown, V, SUM, ,0)
  )
 

Attachments

  • Formula Challenge - 021 - SUMIF in Merged Cells.xlsx
    18.3 KB · Views: 3
You must log in or register to reply here.
Back
Top