SOLVED

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

@Timothy_OM 

 

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.

Date.....Payee/Payor......Amount

 

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]

 

@Timothy_OM 

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.

https://www.mediafire.com/file/8mmko80uz5pa6fr/11_05_21a.xlsx/file

https://www.mediafire.com/file/azli89wuebhs85d/11_05_21a.pdf/file

 

@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):

 

=SUMIF(Table2[Column1],"<44000")

 

 

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)
Solution

@Timothy_OM 

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

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

WINNER WINNER CHICKEN DINNER!!

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)) )