SUMIF where rows are compared

[edward_glyver]

Hi,

I have a column with strings, and an adjacent column with values. I want to apply SUMIF to sum those values, but I only need the values for rows where the text in that particular row is different from the row right below it. I tried a combination of SUMIF and OFFSET, but obviously that wasn't gonna work, as the reference in the OFFSET formula would apply to the whole range, and I don't want that. Is there a way to do this without VBA? Basically, for every row that is different from the row right below it, I want to get the values and have it summed.

Thanks,
Ed

[samba_ravi]

Hi, Welcome to the forum

Please attach a sample excel file

[edward_glyver]

Thanks Siva.
I'm afraid that's going to be a bit problematic now. But imagine this,
Column A
Row 1 UK
Row 2 UK
Row 3 UK
Row 4 Germany
Row 5 Germany
Row 6 Germany
Row 7 France
Row 8 France, etc.

So I'd like to have the values from the B column summed for rows 3 and 6.

[samba_ravi]

Try
=SUMPRODUCT((A1:A8<>A2:A9)*(B1:B8))
or
=SUMPRODUCT((A1:A8<>OFFSET(A1,1,,ROWS(A1:A8),))*(B1:B8))

[edward_glyver]

Great, thanks!

[edward_glyver]

Try
=SUMPRODUCT((A1:A8<>A2:A9)*(B1:B8))
or
=SUMPRODUCT((A1:A8<>OFFSET(A1,1,,ROWS(A1:A8),))*(B1:B8))

By the way, why do you have to specify the height in the offset function?