Subtraction Formula with Certain Criteria

danielleelalouf

Could someone please help me out with a formula. I’m trying to get a formula to subtract the number of tickets requested (from any rows in the sheet) from 24, but only when the date is a specific date (i.e., 01/08/2022). I keep getting errors, but I can’t figure out what my error is though.  

What I've tried and failed with so far is: =COUNTIF([Date for Suite Use]:[Date for Suite Use] = "01/08/2022", 24 - [Number of Tickets Needed]:[Number of Tickets Needed]). I've also tried SUMIF/S and COUNTIFS, but those generate an error as well.

Heather Duff

Hi @danielleelalouf ,

First, make sure your Date for Suite Use column is set as a Date column in the column properties.

When it comes to the formula - are you wanting to do a sum of all of the tickets for January 8, then subtract that from 24? If so, it would look like this:

=24-SUMIF([Date for Suite Use]:[Date for Suite Use],DATE(2022,01,08),[Number of tickets needed]:[Number of tickets needed])

Let me know if this works - or if you were looking for something different.

Best,

Heather

Heather Duff

Hi @danielleelalouf ,

First, make sure your Date for Suite Use column is set as a Date column in the column properties.

When it comes to the formula - are you wanting to do a sum of all of the tickets for January 8, then subtract that from 24? If so, it would look like this:

=24-SUMIF([Date for Suite Use]:[Date for Suite Use],DATE(2022,01,08),[Number of tickets needed]:[Number of tickets needed])

Let me know if this works - or if you were looking for something different.

Best,

Heather

danielleelalouf

Hi Heather,

This was exactly what I needed! Thank you. Is it possible to convert this to a column formula if my Date for Suite Use column has varying dates aside from 1/8/22?

Devin Lee

Hey @danielleelalouf,

Anytime a date is involved with a formula use the DATE function to avoid any issues.

=24 - SUMIF([Date for Suite Use]:[Date for Suite Use], DATE(2022, 1, 8), [Number of Tickets Needed]:[Number of Tickets Needed])

Heather Duff

@danielleelalouf

Yes! Try this for your column formula:

=24-SUMIF([Date for Suite Use]:[Date for Suite Use],[Date for Suite Use]@row,[Number of tickets needed]:[Number of tickets needed])

danielleelalouf

@Heather Duff

Thank you for your help! I don't think I can use that column formula because my sheet is a little tricky. I'll need the formula to pull from the specific date but the specific dates aren't located only in one row. For example there could be multiple rows with the 1/8/22 date with various numbers of tickets requested and I need the sheet to total those.

Heather Duff

@danielleelalouf I would probably consider creating a new sheet and using a cross-sheet reference. Something like this:

New sheet - have a column (NOT primary) set as a date column called Date with the dates you want to total, then a column where you put the 24-sum formula.

In the formula column:

=24-SUMIF({date for suite use column range},Date@row,{number of tickets needed column range})

Where the {named ranges} refer to the entire column associated with the name.

Let me know if this makes sense.