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

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

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

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

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

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

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