title: Multiple Condition Index/Match/Sum using conditions that are not present in all cells
body: <p>Below, please find a screenshot that will provide some context for my question.</p>
  
  <p><img src="https://i.stack.imgur.com/4fLbG.png" alt="enter image description here"></p>
  
  <p>The above is a modified version of output from a reporting tool used by my company.  This report is spit out in Excel 2003 format and I will be manipulating it in Excel2010.  I cannot change the format of the output.  </p>
  
  <p>My issues with the above are twofold.  First, as far as I can tell, the app that generates the report uses nonbreaking spaces everywhere.  So simple MATCH statements on something like "Not Blue" will oftentimes return an N/A because I'm searching using a "normal" space between "Not" and "Blue".  The other issue is that I cannot figure out how to do a SUMIFS using criteria from the first three columns (to total all Data 1 values where I have Level 1/Blue/Green as my criteria, for example).  The cells in columns 1 and 2 are coming over as merged cells and I'd have to manually unmerge them and then copy down the appropriate cell value.  This would be painful.</p>
  
  <p>I am hoping there is some way to get around these limitations so that I can write SUMIF formulas (or INDEX/MATCH or SUMPRODUCT or any other summing/matching formulas) relatively easily against the format being spit out by the report, without having to majorly modify the output format.  My goal is to set up a standard calculations page which would do lookups against the report spit out by my company's application.  To simplify end-user interaction, I want to be able to have them just copy/paste the output from the application into a sheet of my processing tool, which would then figure out the various totals I need.</p>
  
  <p>I am perfectly willing to explore VBA as well as formula-based solutions.</p>