legalhustler
Well-known Member
- Joined
- Jun 5, 2014
- Messages
- 1,171
- Office Version
- 365
- Platform
- Windows
Hello,
I have an array formula that requires control + shift + enter (CSE) but would like to convert it to a normal formula so it doesn't require CSE.
and this one too:
The link below is sample data. The formulas under the header Total # of Contract Awards and Total Contract Value is where the formulas will need to be changed. When this change is done, I should be able to see the correct Cumulative or Non-Cumulative quarter when I select from the drop down in D2 or E2. In addition, I would like to also incorporate the Fiscal Year drop-down in cell F2 to my formula so that I will be able to select a Fiscal Year and either a Cumulative or Non-Cumulative quarter from the drop downs.
Sample Data: https://app.box.com/s/2u44y65v4jfpr4uzybv5
I have an array formula that requires control + shift + enter (CSE) but would like to convert it to a normal formula so it doesn't require CSE.
{=IF(ISNUMBER(MATCH($D$2,{"Q1","Q2","Q3","Q4"},0)),SUM(COUNTIFS(PLogTable[Quarter],"Q"&ROW(INDIRECT("Q1:"&$D$2)),PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000")),IF(ISNUMBER(MATCH($E$2,{"Q1","Q2","Q3","Q4"},0)),COUNTIFS(PLogTable[Quarter],$E$2,PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"),""))}
and this one too:
{=IF(ISNUMBER(MATCH($D$2,{"Q1","Q2","Q3","Q4"},0)),SUM(SUMIFS(PLogTable[Amount],PLogTable[Quarter],"Q"&ROW(INDIRECT("Q1:"&$D$2)),PLogTable[Contract Actions],"Small Purchase",PLogTable[Amount],">=10001",PLogTable[Amount],"<=100000")) + IF(ISNUMBER(MATCH($D$2,{"Q1","Q2","Q3","Q4"},0)),SUM(SUMIFS(PLogTable[Amount],PLogTable[Quarter],"Q"&ROW(INDIRECT("Q1:"&$D$2)),PLogTable[Contract Actions],"BPA",PLogTable[Amount],">=10001",PLogTable[Amount],"<=100000"))),IF(ISNUMBER(MATCH($E$2,{"Q1","Q2","Q3","Q4"},0)),SUMIFS(PLogTable[Amount],PLogTable[Quarter],$E$2,PLogTable[Contract Actions],"Small Purchase",PLogTable[Amount],">=10001",PLogTable[Amount],"<=100000")+IF(ISNUMBER(MATCH($E$2,{"Q1","Q2","Q3","Q4"},0)),SUMIFS(PLogTable[Amount],PLogTable[Quarter],$E$2,PLogTable[Contract Actions],"BPA",PLogTable[Amount],">=10001",PLogTable[Amount],"<=100000")),""))}
The link below is sample data. The formulas under the header Total # of Contract Awards and Total Contract Value is where the formulas will need to be changed. When this change is done, I should be able to see the correct Cumulative or Non-Cumulative quarter when I select from the drop down in D2 or E2. In addition, I would like to also incorporate the Fiscal Year drop-down in cell F2 to my formula so that I will be able to select a Fiscal Year and either a Cumulative or Non-Cumulative quarter from the drop downs.
Sample Data: https://app.box.com/s/2u44y65v4jfpr4uzybv5
Last edited: