Hello,
[I apologize upfront for the length of this, I wanted to give all the detail I could think of]
I am trying to automate my data extraction to support income tax prep where I end up with all the data I need on one Excel worksheet for filling out HR Block or Turbo Tax
I am hoping there is a formula(s) that can do what I need. Its been well over 5 years or so since I tried to put an vba code into my worksheet. I became disabled and had to leave work so I have almost forgot anything you guys have taught me about vba and how to insert it etc. Anyway what I am trying to do is
Background
I use Quicken to enter my finances throughout the year. The last few years I have tracked every receipt and used the total sales tax etc instead of standard deductions. My entries are quite extensive and detailed
I have found having Quicken auto dump into a program like turbo tax doesn’t work all the time and I end up missing things. So I take a quicken report and copy n past it into an Excel worksheet.
My automation I am trying to set up is basically paste that quicken report in the same cell each year. Then have formulas in the columns to the right of that report data or potential report data extrapolating specific data in each column allows me to use SUMIF formulas [=SUMIF($R$175:$R$795,AE27,$AA$175:$AA$795) ] to automatically extrapolate the info and then link all the info into one excel worksheet. I have also used =SUMPRODUCT(--($X$175:$X$795=$AF38),--($AB$175:$AB$795=$AE38),$AA$175:$AA$795) if I have multiple criteria to sum from.
The problem I am having is trying to total the various subcategories of medical [medicine, dental, hospital, doctors, trips, etc]
When a quicken formula is copied and pasted into excel the categories and subcategories are put into one text string separated by a colon.
Examples
Medical:Medicine
Medical:Medicine:Sales Tax
Medical:Dental:Crown:Replacement
Insurance:Dental
Insurance:Medical:Medicare Part B
Medical:Dental:Insurance:Plan Pays
Medical:Trip:Mayo 2011 (1) Jan26-31:Food:Sales Tax
My detailed category and subcategories go 4 or 5 deep at times.
I tried using formulas to split [at the colon] each category and subcategory out into separate cells or columns
But then I found myself summing Insurance:Dental and Medical:Dental:Crown:Replacement data because the word dental ended up in the same column.
I was hoping I could somehow combine the :LEFT function into my sumif formula where along with an if statement I could locate in the column the specific text [example “Medical:Dental” [buried within the text strings] and thereby sum its associated cost located in the same row but different column.
My data is in these columns
Column R ColumnAA
Category Text Strings Amount Paid
The criteria “Medical:Dental” is in AE27
=SUMIF(CriteriaRange;Criteria,SumRange)
The category Text Strings [Criteria Range ] are in $R$175:$R$795
Cell AE27 is where [Criteria] Medical:Dental is
Amount spent [Sum Range) is in column $AA$175:$AA$795
Something like
For my criteria range in the sumif formuala if I could do something like
If ((LEFT(R175,14)=Medical:Dental),SUMIF($R$175:$R$795,AE27,$AA$175:$AA$795),””)
Or
SUMIF((LEFT($R$175:$R$795,14)),AE27, $AA$175:$AA$795))
But I have had no success. I’ve searched Mr Excel and google for sumif’s; for searcing a range of text strings to locate a specific text; for Left Formula
If I saw the answer I was not smart enough to recognize it.
I am going to guess the answer may lie within an Array formula – but Array;s blow my mind about 20% less than VBA. Which both are way out there is my becoming quickly becoming feeble mind that I am left with.
Thanks for your help
Keith
Wannabe Muskie Fisherman and Excel Master
[I apologize upfront for the length of this, I wanted to give all the detail I could think of]
I am trying to automate my data extraction to support income tax prep where I end up with all the data I need on one Excel worksheet for filling out HR Block or Turbo Tax
I am hoping there is a formula(s) that can do what I need. Its been well over 5 years or so since I tried to put an vba code into my worksheet. I became disabled and had to leave work so I have almost forgot anything you guys have taught me about vba and how to insert it etc. Anyway what I am trying to do is
Background
I use Quicken to enter my finances throughout the year. The last few years I have tracked every receipt and used the total sales tax etc instead of standard deductions. My entries are quite extensive and detailed
I have found having Quicken auto dump into a program like turbo tax doesn’t work all the time and I end up missing things. So I take a quicken report and copy n past it into an Excel worksheet.
My automation I am trying to set up is basically paste that quicken report in the same cell each year. Then have formulas in the columns to the right of that report data or potential report data extrapolating specific data in each column allows me to use SUMIF formulas [=SUMIF($R$175:$R$795,AE27,$AA$175:$AA$795) ] to automatically extrapolate the info and then link all the info into one excel worksheet. I have also used =SUMPRODUCT(--($X$175:$X$795=$AF38),--($AB$175:$AB$795=$AE38),$AA$175:$AA$795) if I have multiple criteria to sum from.
The problem I am having is trying to total the various subcategories of medical [medicine, dental, hospital, doctors, trips, etc]
When a quicken formula is copied and pasted into excel the categories and subcategories are put into one text string separated by a colon.
Examples
Medical:Medicine
Medical:Medicine:Sales Tax
Medical:Dental:Crown:Replacement
Insurance:Dental
Insurance:Medical:Medicare Part B
Medical:Dental:Insurance:Plan Pays
Medical:Trip:Mayo 2011 (1) Jan26-31:Food:Sales Tax
My detailed category and subcategories go 4 or 5 deep at times.
I tried using formulas to split [at the colon] each category and subcategory out into separate cells or columns
But then I found myself summing Insurance:Dental and Medical:Dental:Crown:Replacement data because the word dental ended up in the same column.
I was hoping I could somehow combine the :LEFT function into my sumif formula where along with an if statement I could locate in the column the specific text [example “Medical:Dental” [buried within the text strings] and thereby sum its associated cost located in the same row but different column.
My data is in these columns
Column R ColumnAA
Category Text Strings Amount Paid
The criteria “Medical:Dental” is in AE27
=SUMIF(CriteriaRange;Criteria,SumRange)
The category Text Strings [Criteria Range ] are in $R$175:$R$795
Cell AE27 is where [Criteria] Medical:Dental is
Amount spent [Sum Range) is in column $AA$175:$AA$795
Something like
For my criteria range in the sumif formuala if I could do something like
If ((LEFT(R175,14)=Medical:Dental),SUMIF($R$175:$R$795,AE27,$AA$175:$AA$795),””)
Or
SUMIF((LEFT($R$175:$R$795,14)),AE27, $AA$175:$AA$795))
But I have had no success. I’ve searched Mr Excel and google for sumif’s; for searcing a range of text strings to locate a specific text; for Left Formula
If I saw the answer I was not smart enough to recognize it.
I am going to guess the answer may lie within an Array formula – but Array;s blow my mind about 20% less than VBA. Which both are way out there is my becoming quickly becoming feeble mind that I am left with.
Thanks for your help
Keith
Wannabe Muskie Fisherman and Excel Master
