SUMIF statement with two different conditions (Text based and checkbox)

numbers

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.

Best Answer

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

Related Solutions

COUNTIF using two conditions from data inside and outside the dataset

It's probably easiest to add another column with:

AND(A1=1,B1=1)

and then use

COUNTIF(Table 1::C,"=TRUE")

You can hide the extra column if you want by selecting the column's drop-down arrow and clicking "Hide Column".

Minimal example: https://www.icloud.com/iw/#numbers/BAJy4qiGzoTh1VSmyA-BZ16VCStKBAKb9pSE/TEST (link will be removed in 1 week)

PARTIAL SOLUTIONS:

Example using Fudge Tables: https://www.icloud.com/iw/#numbers/BAI89SfR5AMJFjId3zyBzaZuxCGzy9uGo5eE/Fudge_Tables.numbers

Example using Subtables: https://www.icloud.com/iw/#numbers/BAJMsEi-8NDWoPbLaxeBsLTzYZ2Mqnmqo5eE/Subtables

Note that Subtables has a problem with zeroes (0000 translates to a number in the subtable). The best solution (as I did) is to put something like an "X" in every space that doesn't have a number in it in the main table.

Of course, be sure to check the data. The biggest problem with these methods is that they're very manual labor intensive and are very error prone. I blazed through them pretty quickly so I easily could have made a mistake. Just be careful. ;)

MacOS – How to combine two text values from different cells to equal another cell

I would approach this a little differently to (hopefully) simplify things a little!

Let's assume you will use Rows 4, 7, 10, etc to enter the text of your words/sentences (which is the approach in your screenshot).

Let's also assume that you're only going to use Columns C to W to enter your text into (although there's no reason why it can't be more columns, but this is just what I gather from your screenshot).

For this answer, let's assume your Alphabet is in Row 50 and your Substituted letters are in Row 51.

In summary, this means:

the formula you need will have to be entered in every cell from C5 to W5, C8 to W8, C11 to W11, and so on
your alphabet will be in Row 50
your substituted letters will be in Row 51.

So, let's build this using the following steps:

In Row 50 add the letters of the alphabet from A50 to Z50
In Row 51 add your substituted letters
Enter the following formula in cell of C5:

=IFERROR(LOOKUP(C4, $A$50:$Z$50,$A$51:$Z$51),"")

(The reason we're using IFERROR in the formula is so Numbers will ignore any spaces in your sentences, rather than displaying an ugly error icon.)

Copy and paste the formula from cell C5 to your other cells (i.e. cells D5:W5, C8:W8, C11:W11, and so on)
Now start entering your text to be ciphered into cells C4:W4, C7:W7, C10:W10 and so on
As you enter the text, the ciphered result should appear in the cell below. If it's a space, the cell below will remain blank

NOTES:

Be careful to only include the letter and nothing else in each cell of your Alphabet row. Having a space or anything else will cause problems. For example, if you entered your E with a space after it, then Numbers will never find the E because it's looking for an 'E' and not a 'E ', if you get what I mean.
If you have the need to have no substitute value for some letters of the alphabet, then the result will be a 0 (i.e. a zero is displayed) instead of a blank cell. If this applies to you, you could create a conditional highlight to hide the result.
Personally, I would actually put your alphabet and substituted values in another worksheet, but this may not be necessary for your purposes.