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.

[TABLE="width: 1122"]
<tbody>[TR]
[TD][TABLE="width: 1122"]
<tbody>[TR]
[TD]Counter[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Label[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]1-Jan-19[/TD]
[TD]1-Feb-19[/TD]
[TD]1-Mar-19[/TD]
[TD]1-Apr-19[/TD]
[TD]1-May-19[/TD]
[TD]1-Jun-19[/TD]
[TD]1-Jul-19[/TD]
[TD]1-Aug-19[/TD]
[TD]1-Sep-19[/TD]
[TD]1-Oct-19[/TD]
[TD]1-Nov-19[/TD]
[TD]1-Dec-19[/TD]
[TD]Total (Apr-Dec)[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$90,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Counter[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Label[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]1-Jan-19[/TD]
[TD]1-Feb-19[/TD]
[TD]1-Mar-19[/TD]
[TD]1-Apr-19[/TD]
[TD]1-May-19[/TD]
[TD]1-Jun-19[/TD]
[TD]1-Jul-19[/TD]
[TD]1-Aug-19[/TD]
[TD]1-Sep-19[/TD]
[TD]1-Oct-19[/TD]
[TD]1-Nov-19[/TD]
[TD]1-Dec-19[/TD]
[TD]Total (Feb-Nov)[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$10,000[/TD]
[TD]$100,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.