Using sumif where the criteria depends on value of a merged cell

[sushil985]

Hi ..
Please help me with a formula which adds values of COL1 depending on value of the merged cell in COL2..
COL2 always has a merged cell of 4 rows ..
i am trying to use sumif but this takes only the value of the col1 first cell since technically the merged cell is only one cell.
i know that this can easily be done in vba but i am trying to figure out a way using formulae.
Also the cells cannot be unmerged

SUM A=11
SUM B=20

COL1....COL2
1 .........A
5........ *A
...........*A
...........*A
4 ........ B
9 ........*B
6 ........*B
...........*B
1..........B
...........*B
............*B
.............*B
2 ...........A
1 ..........*A
1 ..........*A
1 ..........*A

[oeldere]

Also the cells cannot be unmerged

Why not?

[newdoverman]

These should do it

=SUMIF(B2:B17,"*"&"A",A2:A17)

=SUMIF(B2:B17,"*"&"B",A2:A17)

[sushil985]

thanks but this still doesnt solve the issue .. it still takes only the first row of col1 and not the other 3 rows

[oeldere]

crosspost

http://www.mrexcel.com/forum/excel-q...er-column.html

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sushil985]

Pic attached ..
col1 has values to be added depending on entry of the merged cell in col2.

[newdoverman]

No picture or workbook...Upload a workbook instead of a picture if at all possible. No one can work with a picture.