Sum cells based on multiple criteria with a timeframe requirement

Bria Berger

I am working on trying to sum the time I spend across all my projects and want to sum different columns but only when they meet different criteria. Every formula I work through doesn't seems to work.

I want to sum the Duration of Meeting IF the start date of a subject is within 5 days of the date in the green box that I manually plug in AND that the category matches the subject. Here is a screenshot of my sheet and how I have it set up. Thanks in advance!

Georgie

Hi @Bria Berger,

Since you’re looking to sum values based on multiple criteria, you can use the SUMIFS function. 

To check the date is within 5 days of the date in the green box - NOTE: I’m assuming that you want to include dates within a working week of Monday to Friday, and the date in the green box will always be a Monday, so for the example in the screenshot, you’d want to check dates are between 14th October and 18th October - we can use the following formula:

• =SUMIFS([Duration of Meeting]:[Duration of Meeting], [Start Date]:[Start Date], @cell >= $TODAY$2, [Start Date]:[Start Date], @cell <= ($TODAY$2 + 5))
  

Then we just need to add another range and criterion to check if the category matches the subject. So your full formula would be:

• =SUMIFS([Duration of Meeting]:[Duration of Meeting], [Start Date]:[Start Date], @cell >= $TODAY$3, [Start Date]:[Start Date], @cell <= ($TODAY$3 + 5), Category:Category, @cell = Subject@row)

The $ is used to create an absolute reference to the cell with the green background so that the date in that cell is always the one referenced. You can find out more about the SUMIFS function, using @cell, and creating cell references in the help articles below:

• SUMIFS Function
• Create efficient formulas with @cell and @row
• Create a cell or column reference in a formula

Since the formula contains a cell reference, you won’t be able to convert it to a column formula, but as it contains absolute references, you can drag it down to other cells and won’t need to change anything for it to apply correctly for each row.

Hope that helps!

Georgie

Georgie

Hi @Bria Berger,

Since you’re looking to sum values based on multiple criteria, you can use the SUMIFS function. 

To check the date is within 5 days of the date in the green box - NOTE: I’m assuming that you want to include dates within a working week of Monday to Friday, and the date in the green box will always be a Monday, so for the example in the screenshot, you’d want to check dates are between 14th October and 18th October - we can use the following formula:

• =SUMIFS([Duration of Meeting]:[Duration of Meeting], [Start Date]:[Start Date], @cell >= $TODAY$2, [Start Date]:[Start Date], @cell <= ($TODAY$2 + 5))
  

Then we just need to add another range and criterion to check if the category matches the subject. So your full formula would be:

• =SUMIFS([Duration of Meeting]:[Duration of Meeting], [Start Date]:[Start Date], @cell >= $TODAY$3, [Start Date]:[Start Date], @cell <= ($TODAY$3 + 5), Category:Category, @cell = Subject@row)

The $ is used to create an absolute reference to the cell with the green background so that the date in that cell is always the one referenced. You can find out more about the SUMIFS function, using @cell, and creating cell references in the help articles below:

• SUMIFS Function
• Create efficient formulas with @cell and @row
• Create a cell or column reference in a formula

Since the formula contains a cell reference, you won’t be able to convert it to a column formula, but as it contains absolute references, you can drag it down to other cells and won’t need to change anything for it to apply correctly for each row.

Hope that helps!

Georgie

Bria Berger

@george - YOU are my hero!! LOL!! i was using Sumifs… but i kept getting the order wrong I guess and not referencing the date twice. This works like a charm. Thanks so much for the help.

Georgie

@Bria Berger,

Glad I could help! Yes, SUMIFS and SUMIF have a different syntax in that SUMIFS takes the sum range first, but SUMIF takes the sum range last, so this can be quite confusing!

I recommend reviewing the help box when writing or editing formulas. You can click on different sections of your formula and see which section of the syntax is highlighted. For example, below I clicked on the green cell reference "$TODAY$3", and I can see that "criterion1" is highlighted, so I know that my cell reference is part of the first criterion in the formula.

Hope that makes sense!

Georgie