SUMIF and merged cells

[jasonconlon]

I'm trying to do a SUMIF, but the problem I have is that the criteria I am trying to match is in a merged cell - and only the rows corresponding with the top of that merged cell are considered a match.

For instance, imagine the following 3x7 table, where Apples is a merged cell from A2-to-A4, and Bananas is a merged cell from A5-to-A7:

Type Total
Red 100
Apples Green 150
Yellow 80
Green 60
Bananas Yellow 120
Pink 30

If I want to sum all the apples using =SUMIF(A2:A7,"Apples",C2:C7) then I only get a result of 100 (C2) instead of 330 (C2+C3+C4).

Without unmerging cells, is it possible to write a formula to match and sum these correctly?

 

[Gonzonator1982]

Hello All,

I am new here so forgive me for replying to an old thread. However I have a very simple solution which requires no macros and minimal effort. It takes advantage of the fact that only the first cell in the merged group contains a value, the rest are seen as blank.

Simply add a column to your sheet next to the merged one. Assuming the first merged cell is in A2, then use this formula in B2:

=if(a2="",b1,a2)

This will give you a list which gives always gives the value in the merged cell until that value changes (assuming no breaks). You can hide this later if you wish.

Our individual totals are now in column D. Now copy the column with the merged cells (A) to another column (E), so that it has the same merge layout. Use this formula in the first merged cell in this column, E2:

=SUMIF(B:B,A2,D:D)

Use copy and paste special (formula) to copy this formula into all the other merged cells, ie don’t try to drag copy or you will lose the merging.

You now have a group of merged cells with totals for the other merged cells.

Cheers.

 

[Calir]

Hi

Just to add my tuppence... I have this problem all the time. The way I solve it is to copy your initial data set.. then paste it into MS Word.
Copy the whole thing and paste it back into Excel into a new worksheet.
You can then do a SUM formula as normal as instead of having multiple values in your merged cell you now have just the one.

Its not pretty, and not ideal but who cares... it works!