CODEWITHSUNDEEP
excelexcel-formula
triebark
triebark

Reputation: 11

Sum minimum of corresponding column values - limited to date range

I have a data set with four columns: Start Date, End Date, Scheduled Qty, and Actual Quantity: 

Start Date    End Date    Scheduled Qty    Actual Qty
04/13/15      04/17/15    35               19
04/20/15      04/24/15    35               42
04/27/15      05/01/15    35               41
05/04/15      05/08/15    35               41

I want to find the total actual, except when the actual exceeds the scheduled I want to used the scheduled number.

In an already answered question (Sum minimum of corresponding column values) I found an array formula that works to total the lesser values of each row for the Qty columns (quotes used to display the less than symbol): 

=SUM(IF(C1:C4"<"D1:D4, C1:C4, D1:D4))

This gives me a total for my whole range, but now I'd like to limit it to a date range such as end dates within a given month. I've used SUMIFS in other situations to look at my end dates and only sum data that falls within a given month, but I'm not figuring out how to combine that idea with the one from the array formula.

Any ideas how to make this happen? I'm working in Excel 2013.

Upvotes: 1

Views: 236

Answers (2)

chancea
chancea

Reputation: 5968

To add criteria onto an array function most of the time you are just going to be multiplying the extra condition onto whatever set of conditions you already have.

The reason why this works is simply because we start with our list of numbers we want to sum:

IF(C1:C4<D1:D4, C1:C4, D1:D4) => { 19, 35, 35, 35 }

Then we multiply 1's or 0's to each of the values that meet the extra criteria.

So for an example lets say that we only want to check the quantity of values that have an end date within the month of 4. We can do that with:

MONTH(B1:B4)=4

Just multiply that criteria in the SUM function to basically create a boolean and condition for that criteria:

=SUM(IF(C1:C4<D1:D4, C1:C4, D1:D4)*(MONTH(B1:B4)=4))
= SUM({ 19, 35, 35, 35 } * { 1, 1, 0, 0}) => SUM( {19, 35, 0, 0} ) = 54

This is the same if we want to add n condition's:

=SUM(IF(C1:C4<D1:D4, C1:C4, D1:D4)*(MONTH(B1:B4)=4)*(`Condition2`)*(`Condition3`)...)

You can use any formula or operator that returns a true/false value within your conditions.
Such as: = > < >= <= <> IF(...,TRUE)

To add OR logic as a criteria you need to use addition instead of multiplication and then group them inside a (..)>0 like this:

(((Or_Condition1)+(Or_condition2)+...+(Or_conditionN))>0)

So if we wanted to sum months 4 OR 5 we can write:

=SUM(IF(C1:C4<D1:D4, C1:C4, D1:D4)*(((MONTH(B1:B4)=4)+(MONTH(B1:B4)=5))>0))

Upvotes: 0

xidgel
xidgel

Reputation: 3145

Here's an extension of chancea's approach: Excel's SUM function (and AVERAGE, STDEV, etc.) have the useful behavior of "skipping" over text values. For example AVERAGE(3, 4, "dog", 5) returns 4. You can leverage this behavior nested IF's inside a sum. For instance,

=SUM(IF(MONTH(B1:B4)=4,IF(C1:C4<D1:D4,C1:C4,D1:D4),"NO"))

will sum (a) the lesser of scheduled and actual (b) when the month is 4

This is accomplished by nested IF's. The outer IF is IF(MONTH(B1:B4)=4,...,"NO") [if month <> 4, IF returns text ("NO"), which SUM skips]

The inner IF is the same one that chancea showed. You can nest as many tests/filters for your data as you need

Upvotes: 1

Related Questions