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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Instead of {1,2,3,4,5}
use
ROW(INDIRECT("1:" & MIN(COUNT('PBIT-RiskLog'!D1:D1015),5)))
 

jdhivyan

New Member
Joined
Nov 26, 2009
Messages
4
I dont quite get how your formula applies in my formula.

Could you illustrate with an IF statement in my formula?

thanks
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
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)
 

jdhivyan

New Member
Joined
Nov 26, 2009
Messages
4

ADVERTISEMENT

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)))))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

jdhivyan

New Member
Joined
Nov 26, 2009
Messages
4

ADVERTISEMENT

Yes i tried with Ctrl+shift+enter only
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Perhaps a different approach

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

jddhivyan

New Member
Joined
Oct 24, 2009
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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
Top