# Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

1. ## Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

Need a formula to do the following in the attached file (Games.xlsx):
1. Look in COLUMN C for the entry "Deposit from games"
2. Total the amounts in COLUMN D that match the criteria in step 1
3. Multiply the total from step 2 by .7
4. Look in COLUMN F for the entry 70
5. Total the amounts in COLUMN G that match the criteria in step 4
6. Take the results from step 3 and subtract the results from step 5
------------------------------------------------------------------------------------------
Steps 1&2 results = \$6171.00
Step 3 results = \$4319.70
Steps 4&5 results = \$500
Step 6 results = \$3819.70
------------------------------------------------------------------------------------------
I have tried variations of sumproduct, sumif, and index match. I just can't figure it out. Formula will go in cell K3.

2. ## Re: Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

Hi and welcome back to the forum after being away for almost 7 years

Maybe you didnt quite get the sumif() syntax right?

=SUMIF(\$C\$5:\$C\$14,"deposit from games",D5:D14)*0.7-SUMIF(\$F\$5:\$F\$14,70,G5:G14)

3. ## Re: Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

try:

=(sumifs(d5:d14,c5:c14,"deposit from games")*0.7)-sumifs(g5:g14,f5:f14,70)

4. ## Re: Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

Thank you very much. That works perfectly.

5. ## Re: Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

Happy to help and thanks for the feedback

@ icestationzebra, you can use sunif() here because there is only 1 criteria - not that it really makes a difference)

There are currently 1 users browsing this thread. (0 members and 1 guests)

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