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")))
- Thanks, Hans. It worked. Could you elaborate on why reversing the order helped? I can't wrap my head around it.
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?
