Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
"Lee Harris" wrote in message ... 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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
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 "Lee Harris" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
Yes, very easy. Just drop the salary stuff
=SUMPRODUCT( --($C$1:$C$200=$H2), --($B$1:$B$200=I$1)) best wishes "Lee Harris" wrote in message ... 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? "bernard liengme" wrote in message ... 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 "Lee Harris" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
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? "bernard liengme" wrote in message ... 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 "Lee Harris" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
I meant also to point you to the Help file to learn about Pivot Tables
Bernard "Lee Harris" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
this group is a beautiful thing!
many thanks again :-) "bernard liengme" wrote in message ... Yes, very easy. Just drop the salary stuff =SUMPRODUCT( --($C$1:$C$200=$H2), --($B$1:$B$200=I$1)) best wishes "Lee Harris" wrote in message ... 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? "bernard liengme" wrote in message ... 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 "Lee Harris" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with two conditions?
Also, you may want to take a look at pivot tables which allow real easy
analysis of this stuff. http://www.geocities.com/jonpeltier/...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 :-) ------------------------------*------------------------------*---------------- "Lee Harris" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF function with 2 conditions | Excel Worksheet Functions | |||
SUMIF - 2 conditions - with references | Excel Worksheet Functions | |||
SUMIF with two conditions | Excel Discussion (Misc queries) | |||
sumif with two conditions | Excel Worksheet Functions | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |