Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-04-2016, 08:26 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question Calculating the “total” based on “salary” value and its “type” (multiple IF),

Calculating the “total” based on “salary” value and its “type” (multiple IF),

In the attached screenshot and xlsx file, I wanted to calculate the “total” based on the salary value and its type where each type has a factor. For example, if the salary equals 4 with type a, then the total will be 20 (which is 4x5=20). While if the salary is 7 with type b, then the total will be 56 (which is 7x8=56).

I could figure out that this issue could be resolved by using nested IF but as the types increase the IF formula gets complicated. Is there a smart method by which such issue can be resolved?

Thank you



Best

Jamal
Attached Images
File Type: jpg Clip_400.jpg (117.8 KB, 15 views)
Attached Files
File Type: xlsx Type_Factor_Value_Total.xlsx (8.7 KB, 8 views)
Reply With Quote
  #2  
Old 07-04-2016, 09:32 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like the attached.
Attached Files
File Type: xlsx Type_Factor_Value_Total_2.xlsx (9.0 KB, 11 views)
Reply With Quote
  #3  
Old 07-04-2016, 09:54 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

As an alternative to the formula in the file 'Type Factor Value Total_2' you can use:

=SUMPRODUCT(--(G3=$B$3:$B$10),($C$3:$C$10))*F3

in row 3 and copy down.
Reply With Quote
  #4  
Old 07-04-2016, 11:30 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
As an alternative to the formula in the file 'Type Factor Value Total_2' you can use:

=SUMPRODUCT(--(G3=$B$3:$B$10),($C$3:$C$10))*F3

in row 3 and copy down.
Thank you very much XOR for the help. the first solution works fine for me.
very much appreciated
best
Jamal
Reply With Quote
  #5  
Old 07-05-2016, 12:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Don't quote entire posts unnecessarily. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
How obtain accurate total of hours estimated of all subtasks that are a certain type? pcumming Project 3 05-14-2014 05:45 PM
VLookup formula Problem (salary plus (commission x sales)) topgear2015 Excel 11 06-05-2013 09:02 PM
Calculating a cell value based on many variables malawimick Excel 0 12-14-2011 05:29 PM
How do I type on multiple pics? TimHudson Drawing and Graphics 0 07-28-2011 10:28 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft