SUMIF with two conditions?

  • Thread starter Thread starter Lee Harris
  • Start date Start date
L

Lee Harris

I'm using a formula

=SUMIF(C:D,H2,D:D)


which sums salaries in column D if the company name listed in column C
matches the one listed in H (autofilled down a list of unique organisations)


If column B contains a sub department of the organisation, is it possible to
do a SUMIF that will add up the salaries in D column for all data rows where
the organisation = such and such AND the department = SUCH and SUCH (both
organisation and department would be referenced by the content of cells in
column H2:H20 for organisation and I1:P1 for departments)


tks in advance
 
Lee Harris said:
I'm using a formula

=SUMIF(C:D,H2,D:D)


which sums salaries in column D if the company name listed in column C
matches the one listed in H (autofilled down a list of unique
organisations)


If column B contains a sub department of the organisation, is it possible
to do a SUMIF that will add up the salaries in D column for all data rows
where the organisation = such and such AND the department = SUCH and SUCH
(both organisation and department would be referenced by the content of
cells in column H2:H20 for organisation and I1:P1 for departments)


tks in advance
Click to expand...

It would also be great if I could somehow count the number of items, with a
COUNTIF or something, i.e, how many rows contain Department X for
Organisation Y
 
The organization names are in C1:C200 (change as needed)
B1:B200 has the subdept
The salaries are in D1:D200
H2:H20 is the lookup org list
I1:P1 is the subdept lookuplist

To sum with 2 criteria use SUMPRODUCT. The first sum is found with
=SUMPRODUCT( --($C$1:$C$200=$H2), --($B$1:$B$200=I$1), $D$1:$D$200)

Yes, that is a double negative before the first two parentheses.

Suppose this is cell AA1, then copying it to AA2 will find sums for first
org and second subdept.
Copy across to get the other depts and copy down for other orgs

best wishes

Bernard Liengme
www.stfx.ca/people/bliengme
 
Yes, very easy. Just drop the salary stuff
=SUMPRODUCT( --($C$1:$C$200=$H2), --($B$1:$B$200=I$1))
best wishes
 
Thanks bernard
that works beautifully - is it easy to modify to COUNT the actual number of
items that fit the criteria, rather than summing the salaries?
 
Also, you may want to take a look at pivot tables which allow real easy
analysis of this stuff.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Back
Top