Hello,
I am struggling with a SUMPRODUCT formula that essentially "ANDs" a criteria with multiple "OR" criteria to produce a result. The "OR" is more than one text strings that may exists in a range. I have not been able to make the formula work. I hope someone can point out what is wrong and can provide some explanation on how to correct it or if there is a better way to do this.
To better explain the problem and the result I desire, I have attached a sample Excel file. Column A contains text in each row. Corresponding to each text is a date in Column B.
Cell B11 contains the formula. I first compare the date range (B4:B8) with a threshold date in B1. This produces an array {1,0,1,0,0} where Cell B4 and B6 match the threshold date. I then search column A from A4:A8 looking for the text "CAB" OR "VIC". The second array of the SUMPRODUCT should produce a result {1,0,1,1,0} (which it does not). My thought was when the second array is "ANDED" with the first (date) array, the resulting array would be {1,0,1,0,0}. The sum of this would be 2. Instead, I get a value error.
Thanks.
Bookmarks