Sum the values in one column only unique vauels from another

vbapadawan · Aug 19th 2016

I am using Excel 2003 and am having a hard time doing a seemingly simple task... I have seen many formulas in 2007 up that are easy...why not 2003

All I want to do is to sum the values in one column but only the first/unique instance of items in another column (ignoring blanks)

As you can see below, there are occasionally duplicate cheque numbers and blanks, but I want to only sum the unique ones at the bottom in a "Totals" cell.

*XOR LX* · Aug 19th 2016

Re: Sum the values in one column only unique vauels from another

Hi,

Assuming that data is in A1:B7 (with headers in row 1), array formula**:

=SUM(IF(ROW(A2:A7)-MIN(ROW(A2:A7))+1=MATCH(A2:A7,N(+A2:A7),0),B2:B7))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Advanced Excel Techniques: http://excelxor.com/

vbapadawan · Aug 19th 2016

Re: Sum the values in one column only unique vauels from another

OMG Thank you so much

[Blocked Image: http://www.ozgrid.com/forum/image.php?u=230998&dateline=1386501164] XOR LX

, it worked perfectly. I would have never thought of that. I've never seen the n( what does that do?

Sum the values in one column only unique vauels from another

Participate now!

Share