Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
Hi, I'm not exactly sure how to do this. I want to sum data in column c if it
meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
You cannot do this with the layout you've described. Insert a new column A
and copy the word Legislative as many times as needed for those categories. Likewise with Finance. Now your categories are in column B and the data that was in column C is in D, so use this formula =sumproduct(--(a1:a100="Legislative"),--(B1:b100="Salaries & Wages"),C1:C100) "kraymond" wrote: Hi, I'm not exactly sure how to do this. I want to sum data in column c if it meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
Thanks, I thought that might be the case, but you never know! I appreciate
the speedy response.... "Duke Carey" wrote: You cannot do this with the layout you've described. Insert a new column A and copy the word Legislative as many times as needed for those categories. Likewise with Finance. Now your categories are in column B and the data that was in column C is in D, so use this formula =sumproduct(--(a1:a100="Legislative"),--(B1:b100="Salaries & Wages"),C1:C100) "kraymond" wrote: Hi, I'm not exactly sure how to do this. I want to sum data in column c if it meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
Back again!
OK, my summary sheet is using values from on the next sheet (titled Gen Fund Expenditures). On that source sheet, column A is the department name (e.g. legislative), my column B is the category name (e.g. salaries & wages, benefits, etc.) My budget numbers are in column M. My formula of: =SUMPRODUCT(--('Gen Fund Expenditures'!A5:A845="Legislative"),--('Gen Fund Expenditures'!B5:B845="Salaries & Wages"),M5:M845) returns zero (the numeral). What am I doing wrong? Thanks again! Kassandra "kraymond" wrote: Thanks, I thought that might be the case, but you never know! I appreciate the speedy response.... "Duke Carey" wrote: You cannot do this with the layout you've described. Insert a new column A and copy the word Legislative as many times as needed for those categories. Likewise with Finance. Now your categories are in column B and the data that was in column C is in D, so use this formula =sumproduct(--(a1:a100="Legislative"),--(B1:b100="Salaries & Wages"),C1:C100) "kraymond" wrote: Hi, I'm not exactly sure how to do this. I want to sum data in column c if it meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
Assuming there is just a single category of legislative:
.................A......................C 1.....Legislative 2.....Salaries & Wages.....10 3.....Benefits....................44 4.....etc...........................27 5........................................ 6.....Finance 7.....Salaries & Wages.....57 8.....Benefits....................22 9.....etc...........................99 Lookup Legislative Benefits E1 = Legislative F1 = Benefits =INDEX(C9:INDEX(C1:C9,MATCH(E1,A1:A9,0)),MATCH(F1, A9:INDEX(A1:A9,MATCH(E1,A1:A9,0)),0)) Result = 44 -- Biff Microsoft Excel MVP "kraymond" wrote in message ... Hi, I'm not exactly sure how to do this. I want to sum data in column c if it meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
An improvement as long as each main category has the exact same number and
sequence of sub-categories: E1 = Legislative F1 = Benefits =INDEX(C1:C9,MATCH(E1,A1:A9,0)+MATCH(F1,A2:A4,0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Assuming there is just a single category of legislative: ................A......................C 1.....Legislative 2.....Salaries & Wages.....10 3.....Benefits....................44 4.....etc...........................27 5........................................ 6.....Finance 7.....Salaries & Wages.....57 8.....Benefits....................22 9.....etc...........................99 Lookup Legislative Benefits E1 = Legislative F1 = Benefits =INDEX(C9:INDEX(C1:C9,MATCH(E1,A1:A9,0)),MATCH(F1, A9:INDEX(A1:A9,MATCH(E1,A1:A9,0)),0)) Result = 44 -- Biff Microsoft Excel MVP "kraymond" wrote in message ... Hi, I'm not exactly sure how to do this. I want to sum data in column c if it meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF nesting an AND function
Hi kraymond,
In your example it looks like Legislative has no preceding spaces; and that Salaries & Wages, Benefits, etc. do - to indent them. To determine if something is Legislative or Finance, in D2: =IF(A2="","",IF(ISERROR(FIND(" ",A2,1))=TRUE,A2,D1)) Copy this formula down in column D for all rows with corresponding data. =SUM(IF((A:A=" Salaries & Wages")*(D:D="Legislative"),L10:L20)) enter this formula with Ctrl+shift+enter Hope this helps "kraymond" wrote: Hi, I'm not exactly sure how to do this. I want to sum data in column c if it meets two conditions, both in column a. For example, I have budget data in column a; formatted as: Legislative Salaries & Wages Benefits etc. Finance Salaries & Wages Benefits etc. The actual budget number is in column C. I want to summarize the data by saying that if column a is both legislative and benefits, sum column c. Is there a good way to do this? Thanks all! Kassandra |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF nesting ??? | Excel Worksheet Functions | |||
Nesting sumif functions | Excel Worksheet Functions | |||
Nesting SUMIF formulas | Excel Worksheet Functions | |||
Nesting SUMIF | Excel Discussion (Misc queries) | |||
Nesting Sumif function | Excel Worksheet Functions |