Excel - Writing a Complex Formula

Question

I have the following numbers in the range AI3:AJ41:

34  3
26  3
25  3
24  2
24  2
24  2
24  2
24  2
24  2
24  2
24  2
24  2
22  2
22  2
22  2
22  2
21  2
21  2
21  2
21  2
19  2
19  2
19  2
19  2
19  2
19  2
19  2
19  2
17  2
17  2
17  2
15  2
15  2
15  2
15  2
12  1
12  1
12  1
12  1

The AI column contains values while the AJ column contains the following formula in cell AJ3:

=ROUNDUP(AI3/12,0)

which spans down to AI41.

In cell AJ2 I have the following sum formula

=SUM(AJ3:AJ41)

which results in 77.

I want to write a formula into cell AI2, to get the same result as I now have in AJ2 without using the (helper) column AJ i.e. only using the values of the AI column.

Maybe it can be a VB macro to do is, i don't know...

I've provided a link to my Sample File for easy copy/pasting. Thank you for your time & help.

Answer 1

Updated November 27

I'm still at an airport, and decided to do this problem a stab without VBA/UDF.

An Array function can get you what you want. Type this into your worksheet with CTL SHIFT ENTER for a non-vba solution:

=SUM(ROUNDUP(COUNTIF(A1:AH1,">="&ROW(INDIRECT("A1:A"&MAX(A1:AH1),TRUE)))/12,0))

You can download XLSM file here.

For anyone who ever wishes they could perform VBA "loops" in a regular Excel formula, this is a pretty good example of how to approach as this essentially "loops" through the range with a higher integer each time by using the Row and Indirect functions. Chip Pearson's sight is brilliant for stuff like this.

UPDATED Nov 26

I am sitting bored in an airport, so I will give this another go... I think this custom function will get the OP what they want. Put this in anywhere and you'll get 77. =UMutCustom2(A1:AH1)

The custom function code needed is here:

Function UMutCustom2(rng As Range) As Double
Dim r As Long

For r = 1 To Application.WorksheetFunction.Max(rng)

    UMutCustom2 = Application.WorksheetFunction.RoundUp(Application.WorksheetFunction.CountIf(rng, ">=" & r) / 12, 0) + UMutCustom2

Next r


End Function

original Answer

I included both of these examples in a file here.

Probably easiest to use an array formula. In cell Ai1 put this formula: =SUM(ROUNDUP(AI3:AI999/12,0))

However, after typing the formula YOU MUST HIT CTR SHIFT ENTER!

This will create curly brackets around the formula so when you view the formula it should appear: {=SUM(ROUNDUP(AI3:AI999/12,0))} and does sum to 77 on my version of file.

(good news is that in 2019 Excel's new query engine will not require the CTL SHIFT!)

Alternatively, if you want to make a custom function using vba, you could use this custom function, that would not require ctr shift enter... here's VBA code to make that:

Function UMuTCustomFunc(rng As Range) As Double
    Dim ws As Worksheet, rCell As Range

    Set ws = Sheets(rng.Parent.Name)

    For Each rCell In Intersect(ws.UsedRange, rng).Cells
        UMuTCustomFunc = Application.WorksheetFunction.RoundUp(rCell.Value / 12, 0) + UMuTCustomFunc

    Next rCell

End Function

Answer 2

Sumproduct

If you don't want to use an array formula (too lazy to press CTRL SHIFT ENTER) you can use:

=SUMPRODUCT(ROUNDUP(AI$3:AI$41/12,0))

and press ENTER. The result is 77.

Funny

I didn't even know there was a ROUNDUP function so I used this at first:

=SUMPRODUCT(IF(MOD(AI3:AI41,12)=0,INT(AI3:AI41/12),INT(AI3:AI41/12)+1))

but it doesn't work without entering it as an array formula. This should serve as a reminder that even SUMPRODUCT won't always work as a 'non-array' formula.

Addition

If you want to apply the same principle to the range A1:AH1, use this formula:

=SUMPRODUCT(ROUNDUP($A1:$AH1/12,0))

The result is 87 though.