0

I have SUMIFS formula which does not work, because Criteria2 is a formula, not a condition:

=SUMIFS(Income!$C$3:$C$10000;Income!$D$3:$D$10000;C$2;Income!$A$3:$A$10000;IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0))

Sum_range (numbers to sum up): Income!$C$3:$C$10000

Criteria_range1 (name which needs to match): Income!$D$3:$D$10000

Criteria1 (specified name in first sheet where formula is): C$2

Criteria_range2 (dates): Income!$A$3:$A$10000

Criteria2: IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0)

Criteria2 is that year and month from date specified in cell A3 (first sheet) are the same as year and month from Income sheet A column cells.

Is it possible to include so complicated condition as criteria, or the only way is to calculate year and month before and then use ranges with year and month values for the function?

  • Real quick test - if you replace your formula that's in Criteria2 with the value you want, does your SumIfs() work? – BruceWayne Dec 14 '16 at 1:02
  • Please post some sample data (preferably in text form, as was done here and here; if necessary, as a screen shot), the result you expect (with an explanation), and the result you are getting. But first: I’m trying to make sense of your formula, and I’m failing. What is in Income!$C$3? The first of the numbers that you are (conditionally) adding?  But, if AND(YEAR=YEAR, MONTH=MONTH) is true, Criteria2 evaluates to Income!C$3 — so why are you comparing it to the dates in Income!$A$3:$A$10000? – Scott Dec 14 '16 at 1:51
2

No, it will not work, because Criteria_range2 does not contain the same type of data that you are trying to match with Criteria2.

For example, you might have a date 14/12/2016 in Criteria_range2, and you are trying to get a match with exactly the same date entered in cell A3. Criteria2 will evaluate as:

IF(AND(YEAR(Income!$A3)=YEAR($A$3);MONTH(Income!$A3)=MONTH($A$3));Income!C$3;0)
= IF(AND(2016=2016,12=12),Income,0)
= IF(TRUE,Income,0)
= Income

So your Criteria2 in this example evaluates to Income!C$3, and doesn't match the dates that are in Criteria_range2.

If the date had not matched, Criteria2 would evaluate to 0, which also wouldn't match any data in Criteria_range2.

But even if we change the IF statement to return a date, that date will not always match the dates Criteria_range2. For example, if we changed the Criteria2 function to:

date(year(A3),month(A3))

then the function would not work. We need to enter a day also for date() to evaluate. But adding a day, or simply using A3, means that Criteria2 will only evaluate as true when the exact date matches, not checking only month and year as you want.

Calculating year and month in columns first and using a criteria to check each individually will work.

Or, if you want to be unnecessarily fancy, you could add a single column in Income!E3:E10000 and fill with the formula

=YEAR(A3)&MONTH(A3)

Then, you could make your SUMFIFS:

=SUMIFS(Income!$C$3:$C$10000;Income!$D$3:$D$10000;C$2;Income!$E$3:$E$10000;YEAR(A3)&MONTH(A3))

Formulas do work as SUMIFS criteria, but they need to evaluate to match the data that is contained in the ranges.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.