Need to take sumif result even further

[cvercrus]

ACTUAL SCENERIO: Identify the number of First-Time Freshman students in line (a) who had no financial need and who were awarded institutional non-need-based scholarship or grant aid (exclude those who were awarded athletic awards and tuition benefits). H2A(n) – H2A(q) in 1st attachment CDS-H tab (colored in red).


The methodology in getting these numbers are quite gruesome and will cause anyone to become caffeine dependent (LOL), but seriously, I have gotten part of my formula to work and it’s the second part of my formula (function) that I need help with. I have left ALL of the 493 records to include 58 records that are to be thrown out due to the 2nd step that gives me the 435 actual records where the figures will be derived from.

MANUAL STEPS CURRENTLY TAKEN: FILES ATTACHED: FA2015 (DATA FILE) and SELF HELP (DATA copied and pasted from the PIVOT TABLE using the FA2015 data file) The SELF HELP (FF) red tab is the data I am using to help write my formulas for accuracy since these are the students who met criteria) and the formulas provided below are in the SELF HELP STEP 10 below is my ultimate goal (figures needed to report).

TO FIND SELF HELP for (FF)

1) PIVOT TABLE:
- Select COHORT = "YES" (FT - FF students)
- Select ALL_GIFTS and NEED BASE
2) COPY / PASTE PIVOT DATA: Create a new section as shown to the right
3) SORT BY NEED BASED: Any zero ( 0 ) or negative NEED BASED – DELETE (highlighted in yellow in file)
4) CREATE TOTAL COLUMN: NEED BASED minus ALL_GIFTS = TOTAL (highlighted in green)
5) SORT BY TOTAL COLUMN: To put negative amounts at the top
6) FIND AMOUNT OF NEED RECEIVED:
- Any negative $ amount -- use NEED AMOUNT $ AMOUNT (highlight column) (highlighted in red)
- Any positive $ amount -- use ALL_GIFTS $ AMOUNT (highlight column) (highlighted in red)
7) SUM THE HIGHLIGHTED TOTALS: This gives the $ AMOUNT paid for need (those highlighted in red)
8) FIND HEADCOUNT: Add the number of students that remain from sort
9) FIND TOTAL $ AMOUNT OF SCHOLARSHIPS PAID (H2k): ADD the ALL_GIFTS and NEED BASED totals that are highlighted together (those highlighted in red)
10) FIND AVERAGE FA PACKAGE (H2J): DIVIDE the $ AMOUNT PAID by Headcount =
$6461006.88 + $1827946/435 = $19055.64 (I place large *’s and highlighted figures in red)
$828895.88/435 = $19055.64



TOTAL COLUMN STEP 4 ABOVE (USED TO SORT TO IDENTIFY THE BASED FIGURES (NEGATIVE) AND ALL GIFTS (POSITIVE)

{=(SUM(IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>VALUE("0"),'FA2015'!BK:BK)))))-SUM(IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>VALUE("0"),'FA2015'!BB:BB))))))}

- RESULT figure = $5,753,236.51

- I now need to capture the (NEED BASED) figures where the RESULT figures are "Negative" and (ALL GIFTS) figures where the RESULT figures are "Positive" and add these two together to give me
$ 8,288,952.88


As a workaround to get my report out, I used =IF(AND(BT2="YES",BU2="YES",LEFT(L2,3)<>"ND.",BK2>0),IF(BK2-BB2<0,BK2,BB2),0) (IN FILE FA2015 column BY:BY)

- It seems logical that I can incorporate this step into the SUMIF formula above, but I just don't know how to do so.

- This is the step that I am trying to alleviate since I do not want to create new columns each year>


ADDITIONAL FORMULAS that work, but my dilemma is trying to capture the "Negative" RESULT in BK:BK and the "Positive" RESULT in BB:BB

NEED BASED FIGURES (FORMULA) – This formula gives me the Grand Total of COLUMN BK – I ONLY NEED the “negative $$$ received” from this total
{=(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>VALUE("0"),'FA2015'!BK:BK)))))))} $14,708,080.00 (GRAND TOTAL)

- How do I go about getting the “negative $$$ figures” highlighted in red ($1,827,946.00)


ALL GIFTS FIGURES (FORMULA) - This formula gives me the Grand Total of COLUMN BB – I ONLY NEED the “positive $$$ received” from this total
{=(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>VALUE("0"),'FA2015'!BB:BB)))))))} $8,954,843,49 (GRAND TOTAL)

- How do I go about getting the “positive $$$ figures” highlighted in red ($6,461,006.88)


Somehow, if I can get the Negative $$$ from BK:BK and the Positive $$$ from BB:BB and add them together, I get the $$$ amount to report. What step(s) in the formula(s) above am I missing to get the magic number I need? Is there anyone in the Excel Forum that can solve this?


Your expertise in this matter would cut hours/days from my work load. Many thanks in advance and please feel free to use what I have provided to help others in this quandary

Cris

[Pauleyb]

Hello cvercrus,
I have to say, you run the risk of scaring away a lot of help due to the length of your post. It takes time to work through what you really want, that I think many people just walk away. I, however, appreciate the time you spent in describing everything (and it was necessary) and it just contributed to showing how much you wanted help. I would have hated to waste the time you spent color coding and explaining the issue.

I think I can get you started, and there are a few options. First, the array formulas you are using are memory intensive. That's okay, but at some point they may break if the dataset gets too large.
This is the equivalent SUMIFS formula for your NEED BASED GRAND TOTAL

Please Login or Register  to view this content.

While it still takes some time to parse, it is generally much easier to read, especially if you pass this spreadsheet on to another user (who is likely not going to know about array formulas).

One problem with SUMIFS is that your cannot perform any calculations on the ranges. You want to subtract column BB from column BK. So, then you are back to using array formulas, and this will perform the math to get you the "negative $$$ figures"

Please Login or Register  to view this content.

You can see that what I added was the ('FA2015'!BB:BB-'FA2015'!BK:BK)>0 condition.
But, since array formulas carry some burden, you may want to avoid it by creating a new helper column in your FA2015 sheet. I used column BF (which I realize you probably removed other data from there, but use any column you wish) and in BF2 I typed in
=IF(BK2<=0,0,BK2-BB2)
I then dragged this down for all of the rows with data in them.
I can now use a SUMIFS formula to get the "negative $$$ figures" in the SELF HELP (FF) sheet instead of the 'awkward' array formula:

Please Login or Register  to view this content.

Similar techniques can be applied to your "positive $$$ figures" which I will leave an as exercise for the reader... Your choice if you wish to continue with array formulas or use the helper column and SUMIFS.

[cvercrus]

Thanks Pauley, you have made my weekend!!! The formulas that you provided are very helpful and the 2nd one is the missing link that I was seeking for and got me started. Here is my finished product. Not as pretty as your simplification, but it works all the same. I tried several variations to get the "Positive" 6,461,006.88 for BB:BB, BUT I was able to get the "negative" 2,493,836.61 for BB:BB (1st below) and added to my base formula (2nd below) and got the $6,461,006.88 that I needed. Thus, I added your formula to my workaround (3rd below) and got the magic number $8,288,952.88 that I seeking.

1. GOT THE NEGATIVE FOR BB:BB VERSES THE POSITIVE
{=(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>0,IF(('FA2015'!BK:BK-'FA2015'!BB:BB)<0,'FA2015'!BB:BB))))))))}

2. WORKAROUND TO GET POSITIVE FOR BB:BB - Used my original base formula for BB:BB and subtracted the "negative" to get $6,461,006.88
{=(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>VALUE("0"),'FA2015'!BB:BB)))))))-(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>0,IF(('FA2015'!BK:BK-'FA2015'!BB:BB)<0,'FA2015'!BB:BB))))))))}

3. THE RESULT THAT I WAS SEEKING '
{=(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>0,IF(('FA2015'!BB:BB-'FA2015'!BK:BK)>0,'FA2015'!BK:BK))))))))+(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>VALUE("0"),'FA2015'!BB:BB)))))))-(SUM(IF('FA2015'!H:H="UG",IF('FA2015'!L:L<>"ND.*",IF('FA2015'!BT:BT="YES",IF('FA2015'!BU:BU="YES",IF('FA2015'!BK:BK>0,IF(('FA2015'!BK:BK-'FA2015'!BB:BB)<0,'FA2015'!BB:BB))))))))}

[JohnTopley]

Using SUMIFS is "neater"

e.g.

=SUMIFS('FA2015'!BK:BK,'FA2015'!K:K,"<>ND.*",'FA2015'!BT:BT,"=YES",'FA2015'!BU:BU,"=YES",'FA2015'!BK:BK,">0")-SUMIFS('FA2015'!BB:BB,'FA2015'!K:K,"<>ND.*",'FA2015'!BT:BT,"=YES",'FA2015'!BU:BU,"=YES",'FA2015'!BK:BK,">0")

[Pauleyb]

Those are some monster array formulas. I hope you aren't recalculating results you already have in other cells. For example, if you have the first formula in a cell (let's say in cell A1) then you can just reference A1 in the second and third equations.

Finally, JohnTopely and I are in agreement. SUMIFS is so much better and you should strongly consider switching.

[cvercrus]

No Sir(s), I am applying ALL the steps in one formula and populating it into one cell. Therefore, each year when I refresh the data and save the File as a new year, the column setup will never change (well, unless I change it), but the whole idea is to have ALL my data needed for reporting to populate based on the formulas that I've created automatically once the new data is entered (overriding previous year).

Yes, I agree, it is a monster array formulas, but Pauleyb what you provided was the missing link that I was needing so that I do not have to create additional columns or reference other cells.

Thx to you both - Keep doing what you do with your experience and expertise and offering that to people such as myself. With the little peace that you provided makes my heart sing as well as it will save me a half days work since it is a 4 step process doing it manually which I was able to apply your concept to the remaining 9 areas less than 20 minutes which gave me my numbers within milliseconds AND it works beautifully!!!!

Semper Fi -
Cris