Convert Array Formula to a Normal Formula

[legalhustler]

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.

{=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

 

[mole999]

Waiting to be blown out of the water here

Arrays are the way Excel works, if you don't create the Array you won't return the data

 

[legalhustler]

Waiting to be blown out of the water here

Arrays are the way Excel works, if you don't create the Array you won't return the data

They certainly have their needs and time. My current situation, array formula's are being tampered by users who don't know what they are doing. There is formula's that handle arrays without CSE such as SUMPRODUCT , MATCH, etc. The CHOOSE function can also be utilized in unique ways by using array constants such as i.e. {Q1, Q2, Q3, Q4} but somehow I need it to return the results I am looking for in addition to adding the Fiscal Year field I mentioned above.

Disclaimer: If what I am looking for cannot be accomplished without CSE, can someone modify the formula to include the Fiscal Year field even if it requires CSE.

 

[mole999]

OK, if you use macro recorder on the working formula that will allow it to be reinstated should someone damage it, maybe on a workbook open event, or worksheet select

 

[legalhustler]

Anyone? This is sort of urgent if anyone can please help.

 

[Jonmo1]

Honestly, my best advice is to use helper columns/rows.
I understand wanting to get the whole calculation done with 1 formula.
But there are times when the cost of doing that is too great (formuals that require CSE).
It's up to you to choose (pick your poison)

I would create 1 countifs formula for each value of Q1 Q2 Q3 and Q4, say in G2:G5 for example..
G2: =COUNTIFS(PLogTable[Quarter],"Q1",PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"))
G3: =COUNTIFS(PLogTable[Quarter],"Q2",PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"))
etc..

I really can't imaging wanting to have a 'cumulative' count/sum, without ALSO wanting each individual count/sum as well anyway..


And this part is HIGHLY recommended...even if you ignore the rest....
Put the Match's in their own designated cells, then refer to that cell instead of repeating the match so many times across all your formulas.
so say
Z1: =MATCH($D$2,{"Q1","Q2","Q3","Q4"},0)
Z2: =MATCH($E$2,{"Q1","Q2","Q3","Q4"},0)),
Now replace all of those matches with $Z$1 and $Z$2

Then do

=IF(ISNUMBER($Z$1),SUM(G2:INDEX(G2:G5,$Z$1)),IF(ISNUMBER($Z$2),COUNTIFS(PLogTable[Quarter],$E$2,PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"),""))



This was all just for the first formula, and making it non CSE.
You can apply a similar structure for the 2nd formula as well.

 

[legalhustler]

Honestly, my best advice is to use helper columns/rows.
I understand wanting to get the whole calculation done with 1 formula.
But there are times when the cost of doing that is too great (formuals that require CSE).
It's up to you to choose (pick your poison)

I would create 1 countifs formula for each value of Q1 Q2 Q3 and Q4, say in G2:G5 for example..
G2: =COUNTIFS(PLogTable[Quarter],"Q1",PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"))
G3: =COUNTIFS(PLogTable[Quarter],"Q2",PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"))
etc..

I really can't imaging wanting to have a 'cumulative' count/sum, without ALSO wanting each individual count/sum as well anyway..


And this part is HIGHLY recommended...even if you ignore the rest....
Put the Match's in their own designated cells, then refer to that cell instead of repeating the match so many times across all your formulas.
so say
Z1: =MATCH($D$2,{"Q1","Q2","Q3","Q4"},0)
Z2: =MATCH($E$2,{"Q1","Q2","Q3","Q4"},0)),
Now replace all of those matches with $Z$1 and $Z$2

Then do

=IF(ISNUMBER($Z$1),SUM(G2:INDEX(G2:G5,$Z$1)),IF(ISNUMBER($Z$2),COUNTIFS(PLogTable[Quarter],$E$2,PLogTable[Contract Actions],"Contract",PLogTable[Amount],"<1000000"),""))



This was all just for the first formula, and making it non CSE.
You can apply a similar structure for the 2nd formula as well.

I would rather just have one formula. If it's too difficult to modify without CSE, then a CSE formula is fine. Can you help modify the formula so that it picks up the Fiscal Year field from the Table. If you look at the sample data I provided, ideally I want to be able to pick a Fiscal Year from drop down in F2, then either Cumulative Quarter (D2) or Non-Cumulative Quarter (E2). So the formula needs to incoporate all three.

 

[Jonmo1]

You can probably just change the word SUM to SUMPRODUCT and it would no longer require CSE.

Can you just add another criteria to the count/sumifs for the Year based on F2 ?


sorry, I don't have access to box.com through the firewall.

 

[legalhustler]

You can probably just change the word SUM to SUMPRODUCT and it would no longer require CSE.

Can you just add another criteria to the count/sumifs for the Year based on F2 ?


sorry, I don't have access to box.com through the firewall.

You are correct. Just added the year to my criteria and changed "SUM" to "SUMPRODUCT" in my formula.

Thanks!