+ Reply to Thread
Results 1 to 6 of 6

Modify Equation

  1. #1
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Modify Equation

    Hi

    I have been using the equation below in the attached worksheet. It accomplishes what I am looking to do: sum lines 00100 to 10099 for each of the companies listed in Column B. I am wondering if the formula can be modified so that it performs the required calculation in the cell next to the company number listed in column K. For example, I would like calculation for company 660074 which is listed in column K, line 2 to be performed cell L2 rather than on the line in column L where the number for the next company begins. In other words, could the formula be modified so that the total for company 660074 occurs in column L, line 2.


    [=IF(COUNTIF($B$2:B2,B2)>1,"",SUMPRODUCT(--($B$2:$B$150000=B2),--($D$2:$D$150000>=$H$1),--($D$2:$D$150000<=$I$1),(($E$2:$E$150000="0100")+($E$2:$E$150000="0200")),$F$2:$F$150000))]

    Thank you for taking the time to read and consider this request.

    Al
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Modify Equation

    Not sure I understand your question, can't you just drag the formula over to L2, or cut and paste it?
    Tom S.
    ↙ If you find my reply helpful click on the * down there on the left. Yeah that's it, right there, down on the left
    If your question is resolved, mark it SOLVED using the thread tools.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Modify Equation

    cHANGE THE FORMULA IN l1 TO...
    =IF(COUNTIF($B$2:B2,J1)>1,"",SUMPRODUCT(--($B$2:$B$150000=J1),--($D$2:$D$150000>=$H$1),--($D$2:$D$150000<=$I$1),(($E$2:$E$150000="0100")+($E$2:$E$150000="0200")),$F$2:$F$150000))

    Edit: In J2, copied down, use this to pull out unique PRV NUM's...
    =IFERROR(INDEX($B$2:$B$1000,MATCH(0,INDEX(COUNTIF($J$1:J1,$B$2:$B$1000),0,0),0)),"")

    Then move H1:I1 to H2:I2 and use this in L2, copied down (yur can use K2 if you want)...
    =IF(J2="","",SUMPRODUCT(--($B$2:$B$150000=J2),--($D$2:$D$150000>=$H$2),--($D$2:$D$150000<=$I$2),(($E$2:$E$150000="0100")+($E$2:$E$150000="0200")),$F$2:$F$150000))
    Last edited by FDibbins; 03-24-2013 at 06:02 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Modify Equation

    hI

    Thank you for responding so quickly. The first formula worked perfectly. As a newbie (perhaps forever), I would like to know more about what the edit formula and the last formula are doing?

    Again, thanks for your solution.

  5. #5
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Modify Equation

    thank you for reading my request.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Modify Equation

    Quote Originally Posted by FDibbins View Post
    Edit: In J2, copied down, use this to pull out unique PRV NUM's...
    =IFERROR(INDEX($B$2:$B$1000,MATCH(0,INDEX(COUNTIF($J$1:J1,$B$2:$B$1000),0,0),0)),"")

    Then move H1:I1 to H2:I2 and use this in L2, copied down (yur can use K2 if you want)...
    =IF(J2="","",SUMPRODUCT(--($B$2:$B$150000=J2),--($D$2:$D$150000>=$H$2),--($D$2:$D$150000<=$I$2),(($E$2:$E$150000="0100")+($E$2:$E$150000="0200")),$F$2:$F$150000))
    The 1st part will extract all unique entries in column B, when it runs out of unique entries, it "would" return an error msg, so the iferror() takes care of that

    The 2nd part takes your sumproduct, and bases it off the J2 values instead

  7. #7
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Modify Equation

    Thanks again.

    Al Charbonneau

+ 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