# Thread: Sumif And Formulas (2003 Service pak2)

1. ## 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. ## 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. ## 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. ## Re: Sumif And Formulas (2003 Service pak2)

Change AND to OR in the formula I suggested.

5. ## 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. ## Re: Sumif And Formulas (2003 Service pak2)

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

7. ## Re: Sumif And Formulas (2003 Service pak2)

Does the attached do what you want?

8. ## 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. ## 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. ## 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.

11. ## 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. ## 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. ## Re: Sumif And Formulas (2003 Service pak2)

Is this better?

14. ## 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
•