Sum (iff) with multiple strings issue (1 Viewer)

[BethHawkins]

I am attempting to sum amounts only if a different field in the same table contains a certain amount. Here is the formula I have now:
EE Pre Tax: Sum(IIf([Inelig]![P1 Source]="EE", [Inelig]![P1 Amount]), Sum(IIf([Inelig]![P2 Source]="EE", [Inelig]![P2 Amount]), 0))

I will need to do this for 15 "P1 Amount"s.

I am able to get the function to work for the one P1 Source but as soon as I add the P2 Source, I either get closing parathesis errors or "The expression you entered has a function containing the wrong number of arguments" error. Any suggestions would be GREATLY appreciated.

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Gasman]

Looks like you have an unnormalized database, so you will always be jumping through hoops?

 

[CJ_London]

EE Pre Tax: Sum(IIf([Inelig]![P1 Source]="EE", [Inelig]![P1 Amount]), Sum(IIf([Inelig]![P2 Source]="EE", [Inelig]![P2 Amount]), 0))

your brackets are in the wrong place and you have too many sums- try
EE Pre Tax: Sum(IIf([P1 Source]="EE", [P1 Amount], IIf([P2 Source]="EE", [P2 Amount], 0)))

and I agree with Gasman

 

[BethHawkins]

Thank you! That makes sense and worked alot better! I will look into the unnormalized database issue. Thanks again!

 

[Gasman]

I will look into the unnormalized database issue.

You appear to be using Access with an Excel head on.
The products are completely different and require a different mindset.

Very common for people that come from Excel, even with very good VBA knowledge.

Some excelllent links here https://www.accessforums.net/showthread.php?t=87892&p=510553#post510553

HTH

 

[BethHawkins]

Accurate assessment! I appreciate the extra help! Thank you!