Can SUMIFS calculate data from current month with 2 or more criteria?-CodePudding

I have a spreadsheet that uses a few SUMIFS formulas to calculate data based on 2 criteria and a normal range. Currently I'm having to change this manually every month to capture metrics of tickets reported that meet some of those criteria. Ideally i'd like to implement some logic to only calculate data that has dates from the current month.

Here's my original sumifs:

=SUMIFS($G$2:$G$100,$A$2:$A$100,"primary",B2:B100,"little")
=SUMIFS($G$2:$G$100,$A$2:$A$100,"primary",B2:B100,"panic")

etc..

I was told something to the extent of this would get me where I needed to be, but it doesn't seem to be actually calculating only data with dates within this month.

=SUMIFS($G$2:$G$100,$A$2:$A$100,"primary*",$B$2:$B$101,"little") SUMIFS($G$2:$G$100,$F$2:$F$100,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Any help would be greatly appreciated!!

Spreadsheet_data

CodePudding user response：

Why not try using the Incredibly Versatile SUMPRODUCT Function here,

Formula used in cell I2

    =SUMPRODUCT(($G$2:$G$9)*($A$2:$A$9=$J2)*($B$2:$B$9=$K2)*
     (TEXT($F$2:$F$9,"mmmmyyyy")=$I$1&YEAR(TODAY())))

The SUMPRODUCT function simply multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 ranges or arrays can be supplied.

It may seem boring, complex, and even pointless. But SUMPRODUCT is an amazingly versatile function with many uses. Because it will handle arrays gracefully, you can use it to process ranges of cells in clever, elegant ways. It can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility.

So, let me go through you a bit what the formula does,

Array 1

($A$2:$A$9=$J2) {TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

Array 2

($B$2:$B$9=$K2) {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

Array 3

(TEXT($F$2:$F$9,"mmmmyyyy")=$I$1&YEAR(TODAY()))  

{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

And this is the whole part which is wrapped within SUMPRODUCT

($G$2:$G$9)*($A$2:$A$9=$J2)*($B$2:$B$9=$K2)*(TEXT($F$2:$F$9,"mmmmyyyy")=$I$1&YEAR(TODAY()))

When you select this part and press F9, it evaluates and shows you an array of numbers

{0;0;0;0;0;0;12;0}

Basically its creates an array of TRUE's & FALSE's and then multiplies with the corresponding values to give you the exact output which you need!

Also why you need to use SUMPRODUCT Function ---> Let me tell about it more

It never gives an error for text values unless you are not using a double negative or double minus, I always say SUMPRODUCT is much versatile function than other, it can be clubbed with many other excel functions without any issue.

The double negative (--) is one of several ways to coerce TRUE and FALSE values into their numeric equivalents, 1 and 0. One we have 1s and 0s, we can perform various operations on the arrays with Boolean logic. The table below shows the result in array1,array2, array3 based on the formula above, after the double negative (--) has changed the TRUE and FALSE values to 1s and 0s.

CodePudding user response：

Yep, you can!

You'll need formulas to get the first date of this month and the next month:

Date(Year(Today()), Month(Today())    , 1))  -- first day of this month
Date(Year(Today()), Month(Today())   1, 1))  -- first day of next month

(This wraps the months intelligently i.e. Date(2021, 13, 1) is 2022-01-01)

You can then use these with greater-than and less-than operators within SUMIFS:

= SUMIFS(
    value_column,
    date_column, ">=" & Date(Year(Today()), Month(Today())    , 1),
    date_column, "<"  & Date(Year(Today()), Month(Today())   1, 1)
)

For example:

= SUMIFS(
    G:G,
    F:F, ">=" & Date(Year(Today()), Month(Today())    , 1),
    F:F, "<"  & Date(Year(Today()), Month(Today())   1, 1),
    A:A, "primary",
    B:B, "panic"
)

So here it's picking up the 33 and 57 to calculate the sum of 90.

Why this instead of `SumProduct`?

You can refer to entire columns e.g. G:G rather than a fixed range e.g. G$2:G$9999. SumProduct gives a #VALUE! error if any of the rows contain text, i.e. the column labels in the first row. SumIfs happily ignores the text.

Edit:

There may be a fault in this formula:

=SUMIFS($G$2:$G$100,$A$2:$A$100,"primary*",$B$2:$B$101,"little") SUMIFS($G$2:$G$100,$F$2:$F$100,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Making the format a little friendlier:

= SUMIFS(
    $G$2:$G$100,
    $A$2:$A$100, "primary*",
    $B$2:$B$101, "little"
)
  SUMIFS(
    $G$2:$G$100,
    $F$2:$F$100, ">=" & DATE(YEAR(TODAY()),MONTH(TODAY()),1)
)

It's adding all the Primary Littles from any month, and then also adding all the entries from this month onwards - double-counting any Primary Littles it's already counted.

Why this instead of SumProduct?

Edit:

Why this instead of `SumProduct`?