How to sum one excel column just the dollar values shown here

New Contributor

How to SUM one excel column filtering out everything else, and keeping just the dollar values shown in attached file.

6 Replies



Is this data you import from, say, a credit card or bank statement?


If so, although there would be ways to write a formula to get the dollar figures only, it would be a LOT better if you were to rearrange the data to begin with, so that it forms a standard table with columns as shown here.



So before we affix a bandage to this but not address the underlying wound, is it possible to import or array the data differently. [I am assuming this is just a sample set of data, so doing so with these six items really is not the point]



Excel 365 Pro Plus with Power Pivot and Power Query.

Transform blocks of data into column based Table

and sum with Table feature or PP.

No formulas, no VBA macro.


@Timothy_OM Perhaps a bit unconventional but usually you would just sum column B. But since it contains dates (that are also treated numbers) you could use this (transformed the list to a structured table):





Why 44000? Because Excel treats dates as sequential numbers starting from 1 January 1900. Currently we are about 44000 (plus a few hundred) days away from that date. So, if the likelihood of dollar amount in column B being above USD 44,000 is minimal, you could use the above formula


Otherwise, use Power Query to do the heavy lifting for you, assuming your data contains more that just a few transactions, but with a $ amount, every 4th row.

best response confirmed by allyreckerman (Microsoft)


Not sure on which version of Excel you are, as variant

=SUM( INDEX(B3:B100, ROW(A1:A20)*4-1,1) )


This is exactly what I was looking for -- simple, elegent, beautiful.


Thank you, Sergei.



@Sergei Baklan


 Thank you for your response mathetes.  YES as am tter of fact is it a bank statement.


Thanks everyone for the valuable input.


Best Regards, Timothy_OM

@Timothy_OM , you ae welcome, glad it helped.

If you are on Excel 365 you could use something like

=SUM( INDEX( range, SEQUENCE( INT( ROWS(range) / 4)+1,,3,4)) )