IF Else Statement Help

[Dshaw]

I have a Spreadsheet that has employee Names in Column A Payrate in Column B in Column C Total Hours. I am trying to add a 1.5% increase to the employees who make more than $14 an hour and bring up the employees that make less than to $14 an hour. Then I need it to return the total amount they would make with the increases. I created a IF else statement that works but can't get it to run on a selection from Column B or get it to sum the total.
Here is the statement I was using " =IF(B16<=14,14,B16*1.5%+B16). I belive I need to use an array but I not familiar with how to do that. Any help would greatly be appreciated.

Thanks,
Darren

 

[DanteAmor]

Hi and welcome to MrExcel.

You could put a sample of your data, try to put several records and explain what is the result that you need.
If you have confidential data, replace it with generic data.

Use XL2BB tool, check my signature.

 

[Eric W]

Welcome to the forum!

Try:

=COUNTIF(B2:B10,"<14")*14+SUMIF(B2:B10,">=14")*1.015

 

[Dshaw]

Here is an example Table

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	Wage	Annual Hrs	Annual Wage			Increase Amount		Total Annual increase
2	Greg	$ 13.50	2060	$ 27,810.00			1.50%
3	John	$ 13.75	2020	$ 27,775.00
4	Sally	$ 14.60	2040	$ 29,784.00		If column B is over $14 an hour they get a 1.5%
5	April	$ 15.60	2020	$ 31,512.00		Increase. If it under the get raised to $14 an hour.
6	Juan	$ 13.75	2060	$ 28,325.00		Then it tally's the new annual wage.
7	Tom	$ 14.00	2060	$ 28,840.00
8	Byron	$ 14.25	2020	$ 28,785.00
9	Mike	$ 15.00	2080	$ 31,200.00
10	Total			$ 234,031.00
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=B2*C2
D10	D10	=SUM(D2:D9)

 

[Dshaw]

Welcome to the forum!

Try:

=COUNTIF(B2:B10,"<14")*14+SUMIF(B2:B10,">=14")*1.015

I posted an example to explain better.

 

[DanteAmor]

Try this array formula

Dante Amor
	A	B	C	D	E	F	G
1	Name	Wage	Annual Hrs	Annual Wage		Increase Amount	Total Annual increase
2	Greg	13.50	2060	27,810.00		1.5%	235,850.22
3	John	13.75	2020	27,775.00
4	Sally	14.60	2040	29,784.00
5	April	15.60	2020	31,512.00
6	Juan	13.75	2060	28,325.00
7	Tom	14.00	2060	28,840.00
8	Byron	14.25	2020	28,785.00
9	Mike	15.00	2080	31,200.00
Hoja4

Cell Formulas
Range	Formula
G2	G2	=SUM(IF(B2:B9>14,D2:D9*(1+F2),D2:D9))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Dshaw]

Try this array formula

Dante Amor
	A	B	C	D	E	F	G
1	Name	Wage	Annual Hrs	Annual Wage		Increase Amount	Total Annual increase
2	Greg	13.50	2060	27,810.00		1.5%	235,850.22
3	John	13.75	2020	27,775.00
4	Sally	14.60	2040	29,784.00
5	April	15.60	2020	31,512.00
6	Juan	13.75	2060	28,325.00
7	Tom	14.00	2060	28,840.00
8	Byron	14.25	2020	28,785.00
9	Mike	15.00	2080	31,200.00
Hoja4

Cell Formulas
Range	Formula
G2	G2	=SUM(IF(B2:B9>14,D2:D9*(1+F2),D2:D9))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thanks what you gave me allowed me to correct it to what i needed. this was the final formula "=SUM((IF(B2:B9<=14,14,B2:B9*$G$2+B2:B9))*C2:C9)"

Thanks, for your help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.