SUMIF nesting an AND function

[kraymond]

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

 

[Duke Carey]

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]

Thanks, I thought that might be the case, but you never know! I appreciate
the speedy response....

 

[kraymond]

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

 

[T. Valko]

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

 

[T. Valko]

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))

 

[Peggy]

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="Legislative"),L10:L20))
enter this formula with Ctrl+shift+enter

Hope this helps