Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Extracting datas to use it in SUMIFS

A question about SUMIFS function.


In my formula below, the SUMIFS function shows the 1st test value $Année du séjour. The 2nd test is $ Mois du séjour

Each one refers to 2 hidden columns where we find a number representing the year, and the other one the month extracted from a "date" column.

The couple Month/Year I refer to is the one in the pink case of the Periode column.

User uploaded file



1) This way to do things is rather complicated because it assumes I have created first the 2 columns where we find numbers related to year, and month.

I'd rather prefer to go and check directly the "date" column i'm refering to, watching for the year, and then the month. Something like this ... :


User uploaded file


In this picture the "date" column is in brown.

But obviously SUMIFS has a problem with it; it says: in the SUMIFS function, all interval type values must have same size

Am I missing something, or is it really impossible to use another function to extract the value to test ?



2) Since I have 2 conditions to verify at the same time, is there a special way to use AND function in SUMIF or SUMIFS ?



Thanks all for your help and time.

Lionnel

iMac, OS X Yosemite (10.10)

Posted on Jun 23, 2016 2:43 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 23, 2016 5:03 AM

Hi Lionnel,


I assume your translation is referring to the line below in the SUMIFS description.

  • all interval type values must have same size
  • All collections must be of the same size.

What it means is that each column of test-values (brown) must have the same number of rows as the column of sum-values (blue).

Regards,

Barry

6 replies
Sort By: 
Question marked as Top-ranking reply

Jun 23, 2016 5:03 AM in response to lionnelfromparis

Hi Lionnel,


I assume your translation is referring to the line below in the SUMIFS description.

  • all interval type values must have same size
  • All collections must be of the same size.

What it means is that each column of test-values (brown) must have the same number of rows as the column of sum-values (blue).

Regards,

Barry

Reply

Jun 23, 2016 5:37 AM in response to Barry

Hello Barry,


Yes indeed, this is the issue.

The thing is, the collections have the same number of rows...


In my version, which i assume is the same for you: SUMIFS (Collection of values to sum; collection of values to test; condition; etc ...)


-the question I asked also was: in the collection of values to test, is it possible to use a function refering to a table with same number or rows, like YEAR("date column") ? Or is this why it can't take it (function instead of collection) ?.

Thanks.

Lionnel

Reply

Jun 23, 2016 6:08 AM in response to lionnelfromparis

It is very common to make a working column like this for many situations like you are talking about. And I don't think SUMIFS can work with partial cell contents anyway unless you use wildcards, which i dont' think is going to work with dates. I can try in a bit.


But still, the extra column is a very common way to break out partial cell contents for testing.


Jason

Reply

Jun 23, 2016 8:38 AM in response to lionnelfromparis

Hi Lionnel,


You don't have to set up separate month and year columns if you use SUMIFS like this:


User uploaded file


=SOMME.SIS(Table 1::$B;Table 1::A;">="&DATE(A2;B2;1);Table 1::A;"<"&EDATE(DATE(A2;B2;1);1))


In English:


=SUMIFS(Table 1::$B,Table 1::A,">="&DATE(A2,B2,1),Table 1::A,"<"&EDATE(DATE(A2,B2,1),1))


The idea is to "construct" dates within the formula that SUMIFS then uses as conditions in SUMIFS. One date within the formula is the first day of the month; the other date, derived using EDATE is the first day of the following month. The SUMIFS totals the values in the Amount column for all dates between those two dates.


SG

Reply

Extracting datas to use it in SUMIFS

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.