Formula Needed

jaybar0812 · 2024-05-26T18:30:16-0400

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 · 2024-05-26T18:33:25-0400

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 · 2024-05-26T18:50:10-0400

Hi welcome to the forum!

Perhaps this:

Excel Formula:

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

Copy & paste into cell J10. Then fill down.

jaybar0812 · 2024-05-26T19:02:52-0400

Burrgogi said:
Hi welcome to the forum!

Perhaps this:

Excel Formula:

=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 · 2024-05-26T19:05:16-0400

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:

Excel Formula:

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

jaybar0812 · 2024-05-26T19:11:11-0400

Burrgogi said:
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 · 2024-05-26T19:21:30-0400

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

Excel Formula:

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

Cubist · 2024-05-26T19:26:46-0400

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

-

13

39,000

Yes

1,170

780

Sheet5

Cell Formulas
Range	Formula
I10:I13	I10	=(G10:G13="Yes")F10:F13D6
J10:J13	J10	=(H10:H13="Yes")F10:F13D7
Dynamic array formulas.

jaybar0812 · 2024-05-26T19:39:22-0400

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

Excel Formula:

=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]

Cubist said:
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

Burrgogi said:
12147

Cubist · 2024-05-26T19:43:46-0400

jaybar0812 said:
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.

Formula Needed

jaybar0812

New Member

Excel Facts

jaybar0812

New Member

Burrgogi

Active Member

jaybar0812

New Member

Burrgogi

Active Member

jaybar0812

New Member

Burrgogi

Active Member

Cubist

Well-known Member

jaybar0812

New Member

Cubist

Well-known Member

Similar threads

Forum statistics

Share this page

Formula Needed

New Member

Excel Facts

New Member

Active Member

New Member

Active Member

New Member

Active Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock