Sum multiple cells if a column has a certain number in it

C

Cloughitup

I am trying to sum multiple cells if a number is in another cell. I have a
spreadsheet in which I have assigned numbers to each employee’s type of work
(ranging from 1 to 15) in column A and H. So if Column A has a 2, I need it
to take the value from that row in column F and add it to other number 2 job
duties. Trying to make an executive summary of the amount of time people work
on certain job functions/projects. I would also need it to add the same
number 2 job functions found in Column H taking the number of hours in the
row in column M. Hope that makes sense.
 
R

Reitanos

Sumif would work for you.
The function would look like this for employee number 1:
=SUMIF(A2:F10000,1,F2:F10000)

You can see that the first argument is the range of data, the second
is the thing you are targeting, and the third is the range that should
be summed.
 
B

Bernard Liengme

=SUMIF(A1:A100,2,F1:F100)
will sum all the F values when the corresponding A value is 2

=SUMIF(A1:A100,Z1,F1:F100)
will sum all the F values when the corresponding A value is the same as the
value in Z1

If you wish to sum with more than one criteria (test) you can:
1) use SUMIFS if you are working in Excel 2007
or
2) use SUMPRODUCT (in any Excel version)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
 
C

Cloughitup

How would I add both together? Meaning the =SUMIF(A1:A100,2,F1:F100), worked
but I also want it to add the =SUMIF(H1:H100,2,M1:M100), all in one cell.
Would it be =SUMIF(A1:A100,2,F1:F100)+(H1:H100,2,M1:M100)? Itried that but it
did not work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top