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

• 34 KB Views: 205

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

• Haseeb A

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 and NARAYANK991

Sajan

Excel Ninja
Hi Haseeb,
Here is one more approach...
=SUM(IFERROR(IF(MATCH(LOOKUP(ROW(D),IF(D<>"",ROW(D))),IF(D="A",ROW(D)),0),V),0))

entered with Ctrl + Shift + Enter

-Sajan.

• Haseeb A and NARAYANK991

Sajan

Excel Ninja
Hi,
Here is one more...
=SUM(ISNUMBER(MATCH(LOOKUP(ROW(D),IF(D<>"",ROW(D))),IF(D="A",ROW(D)),0))*V)

entered with Ctrl +Shift + Enter

-Sajan.

• shajan, Haseeb A and NARAYANK991

jeffreyweir

Active Member
That's a really nice challenge, Haseeb. I'll have to think hard on this one.

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

Lori

Active Member
=SUM(IF(LOOKUP(ROW(D),ROW(D)/(D<>""),D)="A",V))

[with Ctrl+Shift+Enter]

Sam Mathai Chacko

Active Member
Just to query. I see a line which says that the solution should be using SUMIF function. I don't see any solution using SUMIF above. So am I misunderstanding something?

• Sandeep_Sawant

Sam Mathai Chacko

Active Member
OK, guess I'll just go with the assumption that SUMIF is not a mandate

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

• Haseeb A

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

Haseeb A

Active Member
Hello Lori, welcome to Chandoo.

=SUM(IF(LOOKUP(ROW(D),ROW(D)/(D<>""),D)="A",V))
Nice formula. Similar to my original formula. Same no: of characters, but a minor difference.

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)

• NARAYANK991 and Haseeb A

Haseeb A

Active Member
Shri,

=SUM((LOOKUP(ROW(D),ROW(D)/(D<>""),D)=D5)*V)
Finally you have it Shri. Exactly my original formula.

• Sajan and NARAYANK991

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]