SUM IF Formula

Corrina Hansen

I'm struggling a bit with a SUM IF Formula and hoping you all can help. I'd like a formula to sum the total cost column of my sheet for rows 236-246 if the start date of rows 236-246 is before the start date of the formula cell row, 220 but after the row above it, 219. Thanks in advance.

KPH

As you have multiple criteria you will need to use a SUMIFS function rather than SUMIF.

The syntax is

=SUMIFS(range, criterion_range1, criterion1, criterion_range2, criterion2,…)

So in your case,

• the range is your total cost column which I assume is called "Total Cost" between cells 236 and 246.
  • [Total Cost]236:[Total Cost]246
• the 1st criterion range is the same rows but in the start date column. I have called this column "Start Date".
  • [Start Date]236:[Start Date]246
• the 1st criterion is that the value in the 1st criterion range is less than the start date in row 220. Start Date must be a date formatted column for this to work.
  • <[Start Date]$220
• the 2nd criterion range is the same as the first
  • [Start Date]236:[Start Date]246
• the 2nd criteria is that the value in that range is greater than the start date in row 219
  • >[Start Date]219

The formula is then:

=SUMIFS([Total Cost]236:[Total Cost]246, [Start Date]236:[Start Date]246, <[Start Date]$220, [Start Date]236:[Start Date]246, >[Start Date]219)

Note - this does not sum rows where the start date is equal to either of the 219 or 220 dates.

In this example the total would be 16

Hope this helps.