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

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

Haseeb A

Active Member
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.
 

Hui

Excel Ninja
Staff member
Thanx Haseeb,
It read as a question and hence my response
I will edit it slightly to make it clearer
 

Sajan

Excel Ninja
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.
 

Haseeb A

Active Member
@ 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...
 

Haseeb A

Active Member
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
=SUM((LOOKUP(ROW(D),IF(D<>"",ROW(D)))=TRANSPOSE(IF(D=2,ROW(D))))*V)
Good solution...!
 

shrivallabha

Excel Ninja
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)
 

Haseeb A

Active Member
10 days have been passed. Just want to say 'Thank you' for every one for your contributions.

Keep 'Excel'ling
 

Sajan

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

Regards,
Sajan.
 

Lori

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

Regards, Lori
 

jeffreyweir

Active Member
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.
 

jeffreyweir

Active Member
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.
 

hymced

New Member
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
 

Peter Bartholomew

Well-Known Member
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]
 
Top