SUM multiple columns when value = a or b or c

[GTESSIER]

Hello,

I am need of some help creating a payroll summary sheet. I need to summarize numbers (in one cell) for vacation, holiday, and bonus if an employee works in dept 100,125, or 175. I tried using sumif for each column but it became too lengthy. (i have a ton of departments and simplified this example)

Is there a way to simply write "sum corresponding values in e f & g when you see 100,125 or 175 in column "a""???

Please help!!!

 

[Hui]

Gtessier

Firstly, Welcome to the Chandoo.org Forums

If you want to add up columns E:G when Column A = 100
=SUMPRODUCT((E2:F11)*(A2:A11=100))

If you want to add up columns E:G when Column A = 100 or 125 or 175
=SUMPRODUCT((E2:G11)*((A2:A11=100)+(A2:A11=125)+(A2:A11=175)))

To understand how these work have a read of:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[GTESSIER]

Gtessier

Firstly, Welcome to the Chandoo.org Forums

If you want to add up columns E:G when Column A = 100
=SUMPRODUCT((E2:F11)*(A2:A11=100))

If you want to add up columns E:G when Column A = 100 or 125 or 175
=SUMPRODUCT((E2:G11)*((A2:A11=100)+(A2:A11=125)+(A2:A11=175)))

To understand how these work have a read of:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

Thanks Hui,


How about if the columns i need to add up are not nested next to each other?

For instance, if I want to sum columns B, E & G where the dept = 100 or 125 or 175?



Thanks for your help!

you are a great resource!

Greg

 

[achu]

Dear Hui sir,
I this formula also will work i hope,


={sum((B2:B11)+(C2:C11)+(D2:D11)+(E2:E11)+(F2:F11)+(G2:G11)*(H2:H11)*(--(A2:A11={"100";"125","175"})}