Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumif And Formulas (2003 Service pak2)

    Hi All,

    I need cells P4 to SUM cells B4 and F4, IF the name in cells D4,E4, H4,I4,J4 match name in L4. I will then fill down to P22

    Any help is appreciated.
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Does this do what you want?
    <code>
    =IF(AND(L4=D4,L4=E4,L4=H4,L4=I4,L4=J4),B4+F4,"")</code>

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Hi Hans,
    thanks for your reply. I did a poor job explaining the need.
    Column L contains the name of each person. I need column P to show the totals of columns B & G, IF the name in column L shows up in any of these columns D,E,H,I,J.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Change AND to OR in the formula I suggested.

  5. #5
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Hi Hans,
    Thanks for bearing with me on this. I've attached the sample sheet with fictitious data. I need column P to reflect the MTD totals. Example "joe" shows up 4 times for a total of $34.00

  6. #6
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    the sheet <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Does the attached do what you want?
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Ok, I would never have understood that from the worksheet without data. Try this array formula (you *MUST* confirm it with Ctrl+Shift+Enter):

    =SUM(IF(($D$4:$D$34=L4)+($E$4:$E$34=L4),$B$4:$B$34 ))+SUM(IF(($H$4:$H$34=L4)+($I$4:$I$34=L4),$F$4:$F$ 34))

  9. #9
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Thanks Hans & Legare,
    I have to be away now for a while, will let you know results later. Hans I attempted to copy formula and could not figure how to "confirm it with Ctrl+Shift+Enter:. I usually right click and paste.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    When you go to past Hans' formula, select the target cell, then click in the formula bar or press F2 to get into edit mode, right click in the formula bar and select paste, then hold down Ctrl+Shift and press Enter to confirm the formula.
    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Hi Legare and Hans, Thanks for all the help, you guys are a godsend. Legare the formula worked perfectly on that sheet. I am attempting to adapt it to my other sheet which needs the formula to evaluate four additional columns F & G then L & M. I modified the formula in cell U4 to evaluate F & G of the required columns, I need it to also evaluate columns L & M. I did not add them yet. Apparently I've made a mistake, the formula returns an incorrect answer. See attached file. Any help is greatly appreciated.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Try this array formula - as Legare mentioned, press F2 to edit the formula, then press Ctrl+Shift+Enter:

    =SUM(IF(($D$4:$D$34=Q4)+($E$4:$E$34=Q4)+($F$4:$F$3 4=Q4)+($G$4:$G$34=Q4),$B$4:$B$34))+SUM(IF(($J$4:$J $34=Q4)+($K$4:$K$34=Q4)+($L$4:$L$34=Q4)+($M$4:$M$3 4=Q4),$H$4:$H$34))

    See attached version.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Is this better?
    Legare Coleman

  14. #14
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif And Formulas (2003 Service pak2)

    Thanks Hans and Legare, they actually both work. Very helpful and instructional. You guys are awesome! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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