Hi there,
I'm looking to create an IF/OR statement. Basically, im looking to have a formula where if the cell in "C" says Bill or John, multiply the cell in "F" by 2. So far I only have the 1 if statement.
=PRODUCT(IFS($C:$C="bill",$F:$F*2))
Also, how would I do this if I wanted to multiply by different numbers. Eg. Bill to muliply by 2 and John to multiply 4
Also, how would I add a 3rd name? Any help would be great!
A simple way to do this, especially since the list of names to be matched and the multipliers is dynamic, would be to create a table and then sum the values of that table. The table itself could be moved, say to the far right hand columns of the worksheet (XFB:XFD) as in the attached file, and/or hidden so that all the end user sees is the result. The formula that yields the result is:The formula that populates the product column is:Formula:
Please Login or Register to view this content.The Name Mtch column is the list of names that you want to find and the Multiplier column is the list of multipliers.Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
thank you, very helpful response
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
Hi again,
After further review, I'm having some difficulty with the formula provided as it doesn't quite work with what I'm trying to do.
I've attached a short workbook of what I would need if I decide to do the table with multiplies. I'm sure this is quite easy, but I'm having difficulty. Any help would be great
Assuming that the multiplier for those names that are not listed is one here is a formula that can be pasted into C2 and copied down:To change the range E1:F3 into a table:Formula:Please Login or Register to view this content.
1) Select one of the cells in the range,
2) Simultaneously press Ctrl and T,
3) Make sure that the correct range is listed and 'My table has headers' is checked in the pop up window,
4) Press OK.
Let us know if you have any questions.
Hi again,
It works great for the names listed in the table; now for example, a name that I do not have listed in a table (because I want the number to be equal to the value...as seen in the worksheet), should I just add the name to the table and have the multipier as 1?
Should not need to do anything. Notice that Joey isn't listed on the table and his total value = his number.
Let us know if you have any questions.
that'll work! Thank you. I'm still configuring this worksheet out so I'll keep this thread alive in order to reference back to it if I have any future issues (i'm sure I will). When I have the kinks ironed out, I'll notify this one as solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks