Formula Needed

[jaybar0812]

Hello Im trying to create a formula that will only calculate the sum of base pay to performance % if there is a yes in column G or H. For instance in the performance amount cell J11 (column J) I want the formula to calculate the award amount of $38,000 * 3% as long as there is a Yes in column G11. I tired this formula but it will not work.

 

[jaybar0812]

Sorry this post posted before I could finish. The formula I have tried is =SUMIF(F11*$D$6,G11,E6). I want the 3% to be fixed but I want the award amount to change based on the base pay

 

[Burrgogi]

Hi welcome to the forum!

Perhaps this:

=SUMIF(G10:G31,"yes",D10:D31)*$D$6

Copy & paste into cell J10. Then fill down.

 

[jaybar0812]

Hi welcome to the forum!

Perhaps this:

=SUMIF(G10:G31,"yes",D10:D31)*$D$6

Copy & paste into cell J10. Then fill down.

Hi Thank you for responding. It is only giving me a value of $0

 

[Burrgogi]

If there is a 'No' in column G, it will display $0. However, if there is a 'Yes', the formula will calculate the bonus pay amount.

What exactly do you want to display if there is a 'No'?

Oops, nevermind. I see I made an error in the column reference. Try this:

=SUMIF(G11:G31,"yes",F11:F31)*$D$6

 

[jaybar0812]

If there is a 'No' in column G, it will display $0. However, if there is a 'Yes', the formula will calculate the bonus pay amount.

What exactly do you want to display if there is a 'No'?

Hi so I want in column J to only calculate the 3% of the base pay if column G has a Yes in it and it remain blank if there is a No. So cell J11 should show $0 and then in cell j12 should have an amount of 870

 

[Burrgogi]

I edited my posting above. Try the revised formula I gave you above.

=SUMIF(G11:G31,"yes",F11:F31)*$D$6

 

[Cubist]

Try:

Book1
	C	D	E	F	G	H	I	J
6	Performance	3%
7	Speicial Commendation Award	2%
8
9					Performance	Speicial Commendation Award
10				38,000	Yes	No	1,140	-
11				29,000	No	Yes	-	580
12				50,000	No	No	-	-
13				39,000	Yes	Yes	1,170	780
Sheet5

Cell Formulas
Range	Formula
I10:I13	I10	=(G10:G13="Yes")*F10:F13*D6
J10:J13	J10	=(H10:H13="Yes")*F10:F13*D7
Dynamic array formulas.

 

[jaybar0812]

I edited my posting above. Try the revised formula I gave you above.

=SUMIF(G11:G31,"yes",F11:F
[QUOTE="Burrgogi, post: 6187014, member: 52733"]
I edited my posting above.  Try the revised formula I gave you above.

[CODE=xls]=SUMIF(G11:G31,"yes",F11:F31)*$D$6

Thank you but that still not working. The amount is coming to 12,147 when it should be 0.
[/QUOTE]

Try:

Book1
	C	D	E	F	G	H	I	J
6	Performance	3%
7	Speicial Commendation Award	2%
8
9					Performance	Speicial Commendation Award
10				38,000	Yes	No	1,140	-
11				29,000	No	Yes	-	580
12				50,000	No	No	-	-
13				39,000	Yes	Yes	1,170	780
Sheet5

Cell Formulas
Range	Formula
I10:I13	I10	=(G10:G13="Yes")*F10:F13*D6
J10:J13	J10	=(H10:H13="Yes")*F10:F13*D7
Dynamic array formulas.

Thank you but this is giving my a #SPILL error

12147

 

[Cubist]

hank you but this is giving my a #SPILL error

It’s a spill array. You need to clear all the cells below. The formula is to be inserted into one cell only.