Using FILTER Function With Multiple Criteria

0
votes

I am having issues using the Filter function in Google Spreadsheet. I am trying to get the following formula to work: 

=SUM(FILTER(Table!$C$2:C, AND(EXACT(Table!$A$2:A,A2)=TRUE, EXACT(Table!$B$2:B,B2)=TRUE))).

However it returns the error:

FILTER has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1, column count: 1

I then tried the following formula:

=SUM(FILTER(Table!$C$2:C, {EXACT(Table!$A$2:A,A2)=TRUE; EXACT(Table!$B$2:B,B2)=TRUE}))

And received the error:

FILTER has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1998, column count: 1

Basically what I am trying to do is to add hours from another sheet ('Table') if and only if the hours are for a specific job and job type. I then try to display the total hours for a job and job type on another sheet ('Job Hours').

I am able to get this function to work:

=SUM(FILTER(Table!$C$2:C, EXACT(Table!$A$2:A,A2)=TRUE))

But when I try to filter for a second condition, I get one of the errors above. Any input is appreciated. Thanks.

1
google-sheets
I also tried this function: =SUMIF(Table!$C$2:C, AND(EXACT(Table!$A$2:A,A2), EXACT(Table!$B$2:B,B2))=TRUE); but nothing got returned. – Evan Pederson

1 Answers

0
votes

I think any of these formulas should deliver the expected outcome:

=SUM(FILTER(Table!$C$2:C, Table!$A$2:A=A2, Table!$B$2:B=B2))

or:

=sumifs(Table!$C$2:C, Table!$A$2:A, A2, Table!$B$2:B, B2)

or:

=SUMPRODUCT( Table!$A$2:A=A2, Table!$B$2:B=B2, Table!$C$2:C)