Using IF

jdhivyan

New Member
Joined
Nov 26, 2009
Messages
4
Hi,

I am summing up the top 5 values using the following formula:

SUM(LARGE(IF('PBIT-RiskLog'!D1:D1015='PBIT-RiskSummary'!A19,'PBIT-RiskLog'!R1:R1015),{1,2,3,4,5}))

But the array fluctualtes in the 'PBIT-RiskLog' sheet. For some, there are 3 values, some 2, some 4. When i use the above default formula, it gives an error for those which are less than array 5.

Could anyone help me with an IF statement which would allow me to sum up if there are less than 5 values.

Like: IF 2 values then apply the formula with array {1,2}, else if 3 values (1,2,3} etc

I need this quite urgently.

Rgds
John
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I dont quite get how your formula applies in my formula.

Could you illustrate with an IF statement in my formula?

thanks
 
Upvote 0
At the end you have the explicit array. Replace that array with the expression that I posted.

...R1015),{1,2,3,4,5}))
becomes
...R1015), ROW(INDIRECT("1:" & MIN(COUNT('PBIT-RiskLog'!D1:D1015),5))) ))

You may have to enter this with Ctrl-Shift-Enter (Cmd+Return for Mac)
 
Upvote 0
i applied to an item which has 3 values to be summed (less than 5). But it gave me #REF! error


=SUM(LARGE(IF('PBIT-RiskLog'!D1:D1005='PBIT-RiskSummary'!A8,'PBIT-RiskLog'!R1:R1005),ROW(INDIRECT("1:"&MIN(COUNT('PBIT-RiskLog'!D1:D1015),5)))))
 
Upvote 0
Control+shift+enter, not just enter...
Code:
=SUM(LARGE(IF('PBIT-RiskLog'!D1:D1015='PBIT-RiskSummary'!A19,
    'PBIT-RiskLog'!R1:R1015),
    ROW(INDIRECT("1:"&MIN(5,
      COUNTIF('PBIT-RiskLog'!D1:D1015,'PBIT-RiskSummary'!A19))))))

Hi,

I am summing up the top 5 values using the following formula:

SUM(LARGE(IF('PBIT-RiskLog'!D1:D1015='PBIT-RiskSummary'!A19,'PBIT-RiskLog'!R1:R1015),{1,2,3,4,5}))

But the array fluctualtes in the 'PBIT-RiskLog' sheet. For some, there are 3 values, some 2, some 4. When i use the above default formula, it gives an error for those which are less than array 5.

Could anyone help me with an IF statement which would allow me to sum up if there are less than 5 values.

Like: IF 2 values then apply the formula with array {1,2}, else if 3 values (1,2,3} etc

I need this quite urgently.

Rgds
John
 
Upvote 0
Perhaps a different approach

=SUM(LARGE(IF('PBIT-RiskLog'!D1:D1015='PBIT-RiskSummary'!A19, 'PBIT-RiskLog'!R1:R1015, 0), {1,2,3,4,5}))
 
Upvote 0
Thanks. It works.

Have another doubt.

a colour in one column shd change corresonding to the inputs in another column. How can this auto update occur?

There shdnt be any visible formula for this.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top