Formula Based Text based on certain criteria/range

[suhas.sharma]

Hello,

I am trying to get automated text (highlighted in color red) based on the selected range and need solution in cells which says Total (Apr-Dec) and Total (Feb-Nov). This label should be based on corresponding month where 1st value in counter row is 1 and corresponding month where last found value of 1 in counter row.

Something like "Total" & Formula & "-" & Formula for Total (Apr-Dec) based on range in counter row.

Thanks in advance genius's out there.

Counter 0 0 0 1 1 1 1 1 1 1 1 1 Label Month 1-Jan-19 1-Feb-19 1-Mar-19 1-Apr-19 1-May-19 1-Jun-19 1-Jul-19 1-Aug-19 1-Sep-19 1-Oct-19 1-Nov-19 1-Dec-19 Total (Apr-Dec) Revenue $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $90,000 Counter 0 1 1 1 1 1 1 1 1 1 1 0 Label Month 1-Jan-19 1-Feb-19 1-Mar-19 1-Apr-19 1-May-19 1-Jun-19 1-Jul-19 1-Aug-19 1-Sep-19 1-Oct-19 1-Nov-19 1-Dec-19 Total (Feb-Nov) Revenue $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $100,000 <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

If you have the AGGREGATE function (Excel 2010 and newer) try:

="Total ("&TEXT(AGGREGATE(15,6,B2:M2/B1:M1,1),"mmm")&"-"&TEXT(AGGREGATE(14,6,B2:M2/B1:M1,1),"mmm")&")"


Otherwise you can use this formula:

="Total ("&TEXT(INDEX(B2:M2,MATCH(1,B1:M1,0)),"mmm")&"-"&TEXT(LOOKUP(2,1/B1:M1,B2:M2),"mmm")&")"

 

[suhas.sharma]

Thanks Eric, it worked. Appreciate all your help.

 

[Eric W]

Glad it works for you. Thanks for the feedback.