Nov 05 2021 10:45 AM - edited Nov 05 2021 11:01 AM
How to SUM one excel column filtering out everything else, and keeping just the dollar values shown in attached file.
Nov 05 2021 11:52 AM
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]
Nov 05 2021 01:12 PM
@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.
Nov 05 2021 01:23 PM
SolutionNot sure on which version of Excel you are, as variant
=SUM( INDEX(B3:B100, ROW(A1:A20)*4-1,1) )
Nov 05 2021 01:35 PM
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
Nov 06 2021 04:02 AM
@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)) )
Nov 05 2021 01:23 PM
SolutionNot sure on which version of Excel you are, as variant
=SUM( INDEX(B3:B100, ROW(A1:A20)*4-1,1) )