6

How can I create a SUMIF statement with multiple criteria?

I have a spreadsheets where I track hours for different clients in one table, and below this table I want to summarize in a summary table the hours per client if that work is finished and if it is billable.

My Statement would say, SUMIF:

  • Textfield ClientName = "InsertClientName"
  • Checkbox for billable hours = TRUE
  • Checkbox for finished work = TRUE

So in my imagination it would have to be:
SUMIF(((ClientName="InsertClientName")&(BillableHours=TRUE)&(FinishedHours=True));Hours)

How can I create a formula like this?

I have browsed the Apple Help Forums and StackExchange, but they mostly show SUMIF Statements with one criteria, or people use IFAND Statements and every row to use that in their SUMIF Statement, but my problem is that I will at least need two criteria in my summary table All hours of ClientX where the hours are checked as billable.

Looking forward to your answers.

7

Just to make an official answer:

Use the SUMIFS() function to use 2 or more conditions.

From the documentation:

The SUMIFS function returns the sum of the cells in a collection where the test values meet the given conditions.

SUMIFS(sum-values, test-values, condition, test-values…, condition…)

2

As pointed out in the comments, I missed the SUMIFS in the functions list because I was focusing on SUMIF.

I am now using SUMIFS and simply attaching all criteria like in a regular SUMIF function and it works like a charm.

Example: SUMIFS(Hours;Billable;true;Finished;true)

Meaning: Summarize all hours If BillableHours-Checkbox is TRUE and if WorkFinished-Checkbox is TRUE.

  • 1
    Great job figuring it out! There are many gems like this in the documentation. – bjbk Nov 25 '17 at 21:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .