• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Excel Formula

J

Jolly

New Member
Hello,


I am a beginner at excel, so i hope i am not asking an obvious question.


I am working on a sheet which calculates payroll.I have a list of people in column A and their wages in column B. How can I sum up the wages of certain people based on their names in column A
 
Sounds like you need a SUMPRODUCT .... assuming your range for names is A1:A10 and the wage is B1:B10. IN cell C1 type the name of a person, say Joe Blogs ,,, in cell D1 type SUMPRODUCT(--(A1:A10=C1),(B1:B10))
 
Welcome to the forum

try sumif function ...

assuming your range for names is A1:A10 and the wage is B1:B10. IN cell C1 type the name of a person, say Joe Blogs

in cell D2 apply the formula->>>

=sumif(A1:A10,C1,B1:B10).....
 
I tried "sumif" formula but in that i can get sum of value for one person only..


Quarry is as follow:


Sr.No Emplyee Designation Wages

1 Joe Site Engineer 20000

2 Thomas Director 100000

3 Sam Site Manager 15000

4 Jack Project Manager 27000

5 Pam Accountant 30000


How can i sum up the wages of site engineer, site manager & Project manager (in a single cel) by using excel formula?
 
Ok, so it sounds like you want to sum the values of more than one job title ,,, you should then definitely use Sumproduct ,,, but expand it a little ,,, same formula ,,, SUMPRODUCT(--(C1:C10="Site Engineer"),--(C1:C10="Site Manager"),--C1:C10="Project Manager"),(B1:B10))


Should work ok
 
Can u please help me with formula as I've already tried sumif formula but it is not working when i use more than one name..? I'm using Excel 2007
 
Hey, Busymanjohn pls find below the sample example...


Sr.No Emplyee Designation Wages

1 Joe Site Engineer 20,000

2 Thomas Director 1,00,000

3 Sam Site Manager 15,000

4 Jack Project Manager 27,000

5 Pam Project Manager 30,000


How can i sum up the wages of site engineer, site manager & Project manager (in a single cel) by using excel formula?
 
Hi,

Try

=SUMIF(H17:H21,H17,I17:I21)+SUMIF(H17:H21,H19,I17:I21)+SUMIF(H17:H21,H20,I17:I21)


Range H17:H21 has designations.

Range I17:I21 has wages.

H17,H19,H20 are the designations for which you are looking to add wages.


Jai
 
Hi Jolly ,,,, Sumproduct(--(C1:C10="Project Manager"),(D1:D10)) will return the sum of the salary for all instances where Project Manager appears in Col C ... you can add additional criteria as follows, Sumproduct(--(C1:C10="Project Manager")--(C1:C10="Site Manager"),(D1:D10))), which will sum the salary for all instances of Project Manager and Site Manager ,, you can add more if you want to.
 
You must log in or register to reply here.
Back
Top