Results 1 to 14 of 14

20070622, 13:41 #1
 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>

20070622, 13:48 #2
 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>

20070622, 14:52 #3
 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.

20070622, 15:10 #4
 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.

20070622, 16:05 #5
 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

20070622, 16:06 #6
 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>

20070622, 16:28 #7
 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

20070622, 16:28 #8
 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))

20070622, 16:51 #9
 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.

20070622, 17:58 #10
 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

20070623, 13:34 #11
 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.

20070623, 14:15 #12
 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.

20070623, 15:15 #13
 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

20070623, 16:12 #14
 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>