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

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: 211

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

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

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

#### 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?

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

##### 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)``

#### Haseeb A

##### Active Member
Shri,

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

#### 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]