+ Reply to Thread
Results 1 to 8 of 8

if(small(index...formula issue

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    if(small(index...formula issue

    hello

    i have a "master' sheet that contains all the data from P1-P12 worksheets (it dumps the data into the master sheet).

    from the master sheet, i then thru a formula figure out how many deals were "won" or are "pending" - see won or spending sheet

    this is my formula in B10 on the won sheet - highlighted in yellow

    {=IF(ISERROR(INDEX(Master!$B$10:$P$6009,SMALL(IF(Master!$P$10:$P$6009=Won!$P10,ROW(Master!$P$10:$P$6009)-ROW(Master!$P$10)+1),COUNTIF(Won!$P$10:$P10,$P10)),B$6)),"",INDEX(Master!$B$10:$P$6009,SMALL(IF(Master!$P$10:$P$6009=Won!$P10,ROW(Master!$P$10:$P$6009)-ROW(Master!$P$10)+1),COUNTIF(Won!$P$10:$P10,$P10)),B$6))}

    basically from the list in the master sheet, im trying to show data all the corresponding metrics for deal taht was "won"

    the issue is...for some reaosn and im not sure why....
    B91, there is only one entry of P1W3 and nothing other data. can someone pls help me? the same thing occurs in the "pending worksheet"

    id be very grateful. thx u
    Attached Files Attached Files
    Last edited by jw01; 01-26-2012 at 12:03 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,624

    Re: if(small(index...formula issue

    Try this in B10, sheet Won:
    Please Login or Register  to view this content.
    B10, sheet Pending:
    Please Login or Register  to view this content.
    Both confimed with Ctrl-Shift-Enter

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,175

    Re: if(small(index...formula issue

    Personally. If I were you I would just write a macro that use the autofilter function to filter anything that has status "won" and make a copy of the report to a new sheet, and then rename the sheet as won.

    I just did the test, and yeah. 80 items using the filter.

  4. #4
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    Re: if(small(index...formula issue

    hello Jiejenn

    if a vba would be possible that would be great, i just dont know the vba language :S

  5. #5
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    Re: if(small(index...formula issue

    hello bebo021999 ...

    i have this formula in column P10:

    {=IF(ISERROR(IF(ROWS($10:10)>COUNTIF(Master!$P$10:$P$6009,Master!$P$4),"",INDEX(Master!$P:$P,SMALL(INDEX(ROW(Master!$P$10:$P$6009)+(Master!$P$10:$P$6009<>Master!$P$4)*10^10,0),ROWS($10:10))))),"",IF(ROWS($10:10)>COUNTIF(Master!$P$10:$P$6009,Master!$P$4),"",INDEX(Master!$P:$P,SMALL(INDEX(ROW(Master!$P$10:$P$6009)+(Master!$P$10:$P$6009<>Master!$P$4)*10^10,0),ROWS($10:10)))))}

    is there anyway i can make this formula shorter?.....and thx u for your formula earlier...it definitely made the calculation on the workbook alot quicker!

    pls let me know

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,624

    Re: if(small(index...formula issue

    You can remove ISERROR(...),"",...
    Please Login or Register  to view this content.
    Does it work?

  7. #7
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    Re: if(small(index...formula issue

    do i need to place the {} around the formula?

    thxs

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,624

    Re: if(small(index...formula issue

    Quote Originally Posted by jw01 View Post
    do i need to place the {} around the formula?
    thxs
    Of course YES because of array formula. You can test with "enter" and get #VALUE.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1