complicated If statement or VBA ?

[Hazem Taha]

Hello Experts,

i have a question multiply based on conditional cells.

required to get the Bonus by multiplying QTY by value, this value based on user and task

example

if user1 and task1, multiply the entire QTY by 15
if user2 and task1, multiply the entire QTY by 10

can any one help ?


EDIT:
https://imgur.com/QCYGSdB

this is link for the image describing

 

[ajamess]

I'm not sure what C2:D5 is representing. You have 4 rows there, is that for the 4 tasks? What are the two columns?

If horizonally user1/task1 corresponds to c2, and user2/task1 corresponds to d2, what does users 3-6 correspond to?

 

[Hazem Taha]

I'm not sure what C2:D5 is representing. You have 4 rows there, is that for the 4 tasks? What are the two columns?

If horizonally user1/task1 corresponds to c2, and user2/task1 corresponds to d2, what does users 3-6 correspond to?

suppose that i have 7 users and 4 tasks, each user can do the 4 tasks. however there are 2 pricing scheme.

lets say that user1 and user2 can do the 4 tasks with pricing scheme 1 ( C2:C5 )
the other users can do the 4 tasks with Scheme 2 ( D2:D5 )

required to automate the "bonus" cell based on QTY multiply rate ( pricing scheme ) according to input of user category and task type

 

[ajamess]

ah, ok I see what you are saying now.

In I2 type

=IFERROR(H2* INDEX(IF(OR(F2=A2,F2=A3),C2:C5,IF(COUNTIF(A4:A7,F2)>0, D2:D5)),MATCH(G2,B2:B5,0)),"")

Tested it out and worked.

 

[Hazem Taha]

many many thanks for your help,

it works fine. appreciated

 

[ajamess]

many many thanks for your help,

it works fine. appreciated

Regarding your pm, It won't let me respond for some reason, so I figured I will here.

I'm not sure which functions you are familiar with, so here is a quick breakdown:

Index-Match: This is similar to a vlookup if that's more familiar, except it's more versatile. It uses the format:

=index('Range where you want answer from',match('Thing you are looking up', 'where you want to look it up' , 0))

The 0 means exact match. So lets do an example: using your table you screenshotted, if I typed in:

=index(c:c,match("user2",a:a,0))

I would get 15. This code is saying my result will be in c:c, and to find what row, I'm going to look in the row number I find "user2" in column A. User2 is the fourth value in column a, so it brings back the fourth value in column c.

So you know that the match part, is going to be looking up the task, in g2, in column b, thats the end part in the formula i originally answered you with.

...match(g2:b2:b5,0)

Question is whether the answer is going to be in c2:c5, or d2:d5. So we need to have logic to figure out based on the user, which pricing scheme they will use.

I did this two different ways,

Pricing Scheme 1- was only two users, user 1 or 2, so I put in the formula.

[COLOR=#333333]IF(OR(F2=A2,F2=A3),C2:C5[/COLOR]

This says if the user we put in F2 is either user1 or user 2 (a2 or a3) then we use c2:c5 for the first part of our index match. So if that is the case: the calculation will run

index(c2:c5,match(g2, b2:b5,0))

However if it is users 3-6 we want it to look up a value out of D2:D5. I didn't to write an or with 4 possibilities. So I had it do:

if(countif(a4:a7,f2)>0, d2:d5))

Instead of it getting long with 4 or's, it looks if our user in f2 is in the last 4 listings, a4-a7. If it is, than the index we are looking up become d2:d5 so the formula evaluated will be:

index(d2:d5,match(g2, b2:b5,0))

Then the index either way is multiplied by H2 like you needed.

I added the

iferror(all the other code, "")

because if either the user or task is blank, it would error out, and it bothers me to have those errors, so I have it just blank instead.


Hope this was enough info, and I could explain clearly enough, I'm not always the best at explaining my reasoning. So let me know if something didn't make sense.