Forum Discussion
How to sum one excel column just the dollar values shown here
How to SUM one excel column filtering out everything else, and keeping just the dollar values shown in attached file.
Not sure on which version of Excel you are, as variant
=SUM( INDEX(B3:B100, ROW(A1:A20)*4-1,1) )
5 Replies
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.
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)) )
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.
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]
