Forum Discussion
Search through columns for a specific result
ElmerOmero Perhaps reverse the order of the conditions?
=IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=3)),"1-7 days",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=2)),"1-24 hours",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=1)),"1-29 minutes","None")))
The row with 2 also contained a 1. If the condition for >=1 comes before the condition for >=2, the formula returns the value intended for >=1 and looks no further. By reversing the order of the conditions, the formula returns the value for >=2 and doesn't evaluate the condition for >=1.
One more question Hans,
I moved the set of values to the AA2:AY2 range of the workbook as I'm using it on a similar location on other excel doc I'm working with, but now the results don't correspond to the ones obtained whiles using the A2:Y2 range. Do you have any idea why this is happening?The original formula uses MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0. This condition is TRUE for columns 1, 6, 11, ... (i.e. A, F, K, ...). For example, the column number of column A is 1, so COLUMN(...)-1 = 0, and MOD(COLUMN(...)-1,5) = 0.
But for column AA, the column number is 27, so COLUMN(...)-1 = 26, and MOD(COLUMN(...)-1,5) = 1.
So you should change the formula to
=IF(SUMPRODUCT((MOD(COLUMN('BASE!'!AA2:AY2)-1,5)=1)*('BASE!'!AA2:AY2>=3)),"1-7 days",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!AA2:AY2)-1,5)=1)*('BASE!'!AA2:AY2>=2)),"1-24 hours",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!AA2:AY2)-1,5)=1)*('BASE!'!AA2:AY2>=1)),"1-29 minutes","None")))