Sumif column "O" for excluding values with duplicate values in column "F"

[jobell]

Good afternoon all,

I'm putting together a macro to format a spreadsheet for upload into Access. I've reached a point, only a few minutes in, that I can't figure out on my own. I'm no expert by any means, but am the only one in my workplace with any VBA knowledge at all, so this task falls on my shoulders alone (hopefully with some of your assistance as well!)

Book1.xls

I need to total all the miles in cell O24, however, in some cases, there are duplicate locations (received more than 1 order). These miles did not occur twice so I need them to only be counted once based on unique values in column "F". The three instances of this in the spreadsheet are highlighted in yellow. Basically, I only want the miles counted once for each unique location.

Thank you all!

[benishiryo]

hi jobell. do you have to use VBA? here's an array formula you can use without VBA.
=SUM(IF(FREQUENCY(MATCH(F2:F23,F2:F23,0),ROW(F2:F23)-1)>0,O2:O23))
you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

i'm not really good in VBA, but it would look something like this if you need it:

Please Login or Register  to view this content.