Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment additionner les valeurs entre deux dates dans Excel?

Lorsqu'il y a deux listes dans votre feuille de calcul comme capture d'écran de droite, l'une est la liste des dates, l'autre est la liste des valeurs. Et vous voulez résumer les valeurs entre deux plages de dates uniquement, par exemple, additionner les valeurs entre 3/4/2014 et 5/10/2014, comment pouvez-vous les calculer rapidement? Maintenant, je vous présente une formule pour les résumer dans Excel.


Somme des valeurs entre deux dates avec formule dans Excel

Heureusement, il existe une formule qui peut résumer les valeurs entre deux plages de dates dans Excel.

Sélectionnez une cellule vide et saisissez la formule ci-dessous, puis appuyez sur Entrer bouton. Et maintenant, vous obtiendrez le résultat du calcul. Voir la capture d'écran:

=SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"<="&E3)

Notes: Dans la formule ci-dessus,

  • D3: D22 est la liste de valeurs que vous résumerez
  • B3: B22 est la liste de dates sur laquelle vous allez additionner
  • G3 est la cellule avec la date de début
  • G4 est la cellule avec la date de fin
ruban de note La formule est trop compliquée à retenir? Enregistrez la formule en tant qu'entrée de texte automatique pour la réutiliser en un seul clic à l'avenir!
Lire la suite ...     Essai gratuit

Additionnez facilement les données de chaque exercice, de chaque semestre ou de chaque semaine dans Excel

La fonctionnalité de regroupement de temps spécial de tableau croisé dynamique, fournie par Kutools for Excel, est capable d'ajouter une colonne d'aide pour calculer l'année fiscale, le semestre, le numéro de la semaine ou le jour de la semaine en fonction de la colonne de date spécifiée, et vous permet de compter facilement, additionner , ou des colonnes moyennes basées sur les résultats calculés dans un nouveau tableau croisé dynamique. Essai gratuit complet de 30 jours!

Kutools pour Excel - Comprend plus de 300 outils pratiques pour Excel. Essai gratuit complet de 30 jours, aucune carte de crédit requise! Achetez-le Maintenant


Somme des valeurs entre deux dates avec filtre dans Excel

Si vous devez additionner des valeurs entre deux dates et que la plage de dates change fréquemment, vous pouvez ajouter un filtre pour la plage donnée, puis utiliser la fonction SOUS-TOTAL pour additionner entre la plage de dates spécifiée dans Excel.

1. Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur la touche Entrée.

= SOUS-TOTAL (109, D3: D22)

Remarque: Dans la formule ci-dessus, 109 signifie la somme des valeurs filtrées, D3: D22 indique la liste de valeurs que vous allez résumer.

2. Sélectionnez le titre de la plage et ajoutez un filtre en cliquant sur Data > Filtre.

3. Cliquez sur l'icône de filtre dans l'en-tête de la colonne Date et sélectionnez Filtres de date > Entre. Dans la boîte de dialogue Filtre automatique personnalisé, saisissez la date de début et la date de fin selon vos besoins, puis cliquez sur le bouton OK bouton. La valeur totale changera automatiquement en fonction des valeurs filtrées.


Articles Liés:


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    SANJAY VAGHELA · 1 months ago
    INVOICE DATE    TOTAL
    01-09-2021    19817
    02-09-2021    44791
    03-09-2021    26780
    03-09-2021    58500
    03-09-2021    2029

    04-09-2021    79260
    04-09-2021    2120
    04-09-2021    9187
    04-09-2021    5698
    04-09-2021    1400
    04-09-2021    1900
    04-09-2021    7500
    04-09-2021    830


    i have above data in one sheet and i need date wise total value as below in another sheet,
    01-09-2021    19817
    02-09-2021    44791
    03-09-2021    87309
    04-09-2021    107895

    pls give formula details for it
    sanjay
  • To post as a guest, your comment is unpublished.
    kellytte · 1 years ago
    Hi Jett,
    You can use the SUMIFS function to sum by multiple criteria simultaneously.
    =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…, criteria_rangeN, criteriaN)
  • To post as a guest, your comment is unpublished.
    Jett · 1 years ago
    What if there are multiple columns to sum up like one for interest, one for penalty, and one for principal amount? and it still need to be between two dates
  • To post as a guest, your comment is unpublished.
    gerardy · 1 years ago
    hi i have a question related to your topic,
    if the case is like this
    DATE AMOUNT
    17 SEP 70.000
    60.000
    18 SEP 30.000
    19 SEP 20.000
    10.000
    IF i use sum if between date 17 sept to 19 sept the 60.000 & 10.000 can't be include in the total.
    what formulas should i use to include the 60.000 and 10.000 in the total?
  • To post as a guest, your comment is unpublished.
    Ejaz · 1 years ago
    Hi, i have a question related to sumifs:

    want to calculate a sum between two dates e.g. start of date and end of date, but at times there are payments which were made in a new month but connected to the expense month, For example if a payment date is Oct 5th but the payment is for September and I would like to show this payment for the month of September, the formula should ignore the Oct 5th date and consider the payment in September. How do i do that
  • To post as a guest, your comment is unpublished.
    John Morrissey · 1 years ago
    Help! I can only get this formula to work if I convert to a MM/DD/YYYY format but all of my data is DD/MM/YYYY. The cells are formatted correctly but the formula doesn't work unless I change from eg. 14/01/2020 to 01/14/2020.
  • To post as a guest, your comment is unpublished.
    Ayesha · 2 years ago
    Hi,

    can you help me to calculate two working experiences in excel.

    Ex: 12 Yeas 3 Months 5 Days - CDB Finance company

    9 Years 4 Months 7 Days - Commercial Bank

    These are two working experiences in two working places of one person. how to sum these two experiences as a total experience in excel. Please help me.
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      Hi Ayesha,
      We can suppose the start is 1900/1/0, and add the two date periods to get the end date, and then get the difference between the start date and end date as attached image shown.
      Two formulas used:
      In Cell B7: =DATE(YEAR(0)+B4+B5,MONTH(0)+C4+C5,DAY(0)+D4+D5)
      In Cell B9: =DATEDIF(B1,B7,"Y")&" Years, "&DATEDIF(B1,B7,"YM")&" Months, "&DATEDIF(B1,B7,"MD")&" Days"
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    Hi all,

    What if one would like to make a date range non-mandatory?

    Meaning, let's say that I have two cells with dates in them that are used as my beginning and end date threshold, but when those cells aren't populated, the sum for all dates is pulled from the data set.

    My formula (below) has two match criteria with the date ranges at the back of the formula. What I'm trying to do is only filter the sum by the date range if a condition is met (ideally a cell that acts as select Yes or No on "Do you want to limit the sum to a specific date range?"

    =SUMIFS(BURN_DATA!$S:$S,BURN_DATA!$R:$R,$A12,BURN_DATA!$H:$H,I$3,BURN_DATA!O:O,">="&I9,BURN_DATA!O:O,"<="&J9)


    THANKS IN ADVANCE!
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    Aloha, I'm trying to figure out how to sum another column of units, when one column of dates is sequential. Example: I have rows with dates, Mar 1, Mar 2, mar3, then marc 6,7, then march 10.
    I need to sum the corresponding units in of another column for mar 1-3, mar 6-7 and mar 10.
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      Hi David,
      You can use methods introduced in this webpage to sum each period one by one.
      Or you can add a helper column before the date column as attached image shown, and then apply the Subtotal feature.
  • To post as a guest, your comment is unpublished.
    breadnjam · 2 years ago
    I am trying to create a holiday calendar to calculate the following information:

    Start Date:
    End Date (if there is one, otherwise calculate on the entire holiday year_
    No of Holidays
    Holiday year runs from 01/04 to 31/03 with an entitlement of 20 days plus bank holidays

    any help is much appreciated
  • To post as a guest, your comment is unpublished.
    Kasim · 2 years ago
    Hello there,
    I am a sales man at hotel and I want to create a rate plan for different dates and room types,
    for example
    10.08.19 rate 50 usd per person,
    11.08.19 rate 45 usd per person
    12.08.19 rate 55 usd per person
    13.08.19 rate 60 usd per person
    14.08.19 rate 60 usd per person

    when guest ask me 2 different room types for example family room =pp rate x 3
    and standard room =pp rate x 2

    check in 10.08 and for 5 nights.. On rate plan I filled all dates rates, but I cannot arrange for room types..

    could you help someone to me pls..
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      So what’s your problem?
  • To post as a guest, your comment is unpublished.
    Steve · 2 years ago
    I'm trying to sum the monthly data, in column E, between the Date in Q4 and R4, using the dates in column A. All I get is 0 for an answer. Am I making a mistake or is there a bug in Excel?

    =SUMIFS('Major business figures'!$E:$E,'Major business figures'!$A:$A,">"&Analysis!Q$4,'Major business figures'!$A:$A,"<="&Analysis!R$4)
    • To post as a guest, your comment is unpublished.
      Thomas G Ferguson · 2 years ago
      I am have the same issue. I am also trying to use the SUMIFS function
      =SUMIFS(Summary!N$6:N$148,Summary!G$6:G$148,>12/31/2018,Summary!G$6:G$148,<2/1/2019)

      Seem to hang up on criteria1
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      Hi Steve,
      If you just need to sum data in one month, just change the start date to the beginning of the month, and change the end date to the last date of the month.
      If you need to sum data monthly across multiple months, this article may ease your work https://www.extendoffice.com/documents/excel/3459-excel-sum-by-month-and-year.html
  • To post as a guest, your comment is unpublished.
    Yaroslav · 3 years ago
    Hello Excel gurus,

    quick question. I`m trying to sum amounts that are in date range but all i`m trying is useless. The task is the following:

    I have column with trade amounts (more then 1 hundred lines, means different trades). Each trade have booking date and Maturity date. First trade was done on 27.04.2017 and last one 21.11.2018. So, I need to sum trades amounts for each and every date separately starting from 27.04.2017 and till 21.11.2018. How to type the formula correctly having mentioned conditions on top?

    Appreciated in your help.

    Thansk
  • To post as a guest, your comment is unpublished.
    vanji · 3 years ago
    hai Friends I have one dout, how to calculate how many dates between one date to another date.
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      Hi vanji,
      Just apply the formula =later date – earlier date
  • To post as a guest, your comment is unpublished.
    Hayden · 3 years ago
    does the date format matter?
    The formula when i type the 5th July as 5/7/2018, the formula is picking up 7th May instead.
  • To post as a guest, your comment is unpublished.
    Accountant · 3 years ago
    Hi there,


    I want to use a IF formula to pick up the number in a cell, only if the dates in which it rates to fall between two dates. Eg if the start date is between the two dates, then pick up a number from the cell.



    Help
    • To post as a guest, your comment is unpublished.
      kelly001 · 3 years ago
      Hi Accountant,
      For example:
      A1 is the first date 2018/2/27, A2 is the last date 2018/4/13, and you will see if the date in C1 falls in the date range. In this case, you can use this formula =IF(AND(C1>A1,C1
  • To post as a guest, your comment is unpublished.
    Bogdan Mateescu · 3 years ago
    Very helpful, thanks!
  • To post as a guest, your comment is unpublished.
    Darren · 4 years ago
    If I wanted to incorporate another IF into this formula how would I do it? My formula looks like this:

    =SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52)
    I also want to include a variable in CELL D52 from the range of D2:D51.
    • To post as a guest, your comment is unpublished.
      IMRAN AHMAD · 3 years ago
      Can you do like this
      =SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52,D2:D51,D52)
    • To post as a guest, your comment is unpublished.
      kelly.extendoffice@gmail.com · 4 years ago
      Hi Darren,
      If you are not familiar with the SUMIFS function, you can click Formulas > Insert Functions, type SUMIFS in the search for a function box, and click the Go button, and then specify as much criteria as you need in the Function Arguments dialog box. See screenshot:
  • To post as a guest, your comment is unpublished.
    Sharon · 4 years ago
    =SUMIFS(Expenses!D6:K300,Expenses!C6:C300,"> ="&References!B5,Expenses!C6:C300,"< ="&References!C5)

    returns #VALUE. Where am I going wrong? Expense tab, column C has dates. Expenses tab columns D thru K have the $$ amounts that need to be summed. References tab B5 has beginning date. References tab C5 has ending date.
    • To post as a guest, your comment is unpublished.
      kelly.extendoffice@gmail.com · 4 years ago
      Hi Sharon,
      There are two things you need to pay attention:
      (1) Remove space between > and =
      (2) Check if the dates are not stored as text. If stored as text, convert them to date/number.
  • To post as a guest, your comment is unpublished.
    hardik · 4 years ago
    DATED wgt
    09-05-2017 0.71
    30-05-2017 0.55
    07-06-2017 0.53
    07-06-2017 0.50
    07-06-2017 0.36
    07-06-2017 1.01
    08-06-2017 0.80
    13-06-2017 0.51
    13-06-2017 0.50
    13-06-2017 1.02
    14-06-2017 3.01
    15-06-2017 1.00
    15-06-2017 0.71
    19-06-2017 0.54
    24-06-2017 1.40
    26-06-2017 1.61
    27-06-2017 2.53
    30-06-2017 0.70
    04-07-2017 0.30
    04-07-2017 0.30
    04-07-2017 1.30
    04-07-2017 0.50
    05-07-2017 0.61
    10-07-2017 1.01
    10-07-2017 0.60
    10-07-2017 0.70
    12-07-2017 0.50

    i want to calculate date wise weight and convert to month



    Total month
    May -
    June -
    July -
    • To post as a guest, your comment is unpublished.
      kelly.extendoffice@gmail.com · 4 years ago
      Hi Hardik,
      Do you mean summing by month? Methods introduced in this article may help you:
      https://www.extendoffice.com/documents/excel/3181-excel-sum-by-week-month-quarter-year.html
  • To post as a guest, your comment is unpublished.
    Deepak Nandwanshi · 4 years ago
    thanks bro it's working
  • To post as a guest, your comment is unpublished.
    CPY · 4 years ago
    No idea why the formula is not working :( I am using it exactly. When I copy the sample data it works but, when I put in my data it fails. I never had an issue with sumifs ...weird Here is my formula....=SUMIFS('KPI Data'!$G:$G,'KPI Data'!$E:$E,">="&$C$4,'KPI Data'!$E:$E,"
    • To post as a guest, your comment is unpublished.
      Tariq · 4 years ago
      you need to change ">=" to "> =". The code they provided is missing the space.
  • To post as a guest, your comment is unpublished.
    Faheem · 4 years ago
    Thanks a bunch. Works perfectly well.
  • To post as a guest, your comment is unpublished.
    Pavan · 4 years ago
    I want the formula for exactly reverse criteria.. I want to arrive a value of a date.. I have from and to dates and value..
  • To post as a guest, your comment is unpublished.
    tabish misbah · 5 years ago
    hi i want to apply this formula on other sheet while my values and date are on other sheet how could this be possible please
  • To post as a guest, your comment is unpublished.
    Chantal · 5 years ago
    Hi,

    I require a formula which is kind of the reverse of the =SUMIFS(B2:B8,A 2:A8,">="&E2,A2 :A8,"
  • To post as a guest, your comment is unpublished.
    Chris · 5 years ago
    Hi,

    I need a formula that will display a total if the start and finish dates are between dates shown in a row.

    I have tried various IF( AND...) formulas but get caught out on either the start or finish dates
  • To post as a guest, your comment is unpublished.
    Robert · 5 years ago
    Good day,

    Info to note
    * working with 10 products (Prices vary every day) prices in worksheet 1-"Prices" (Colum A dates) (Products Row 2C-M) and quantity sold for the day in sheet 2 "Book 34" (each book consist of 100 invoices (1-100 in row B dates in A) products in row 2 (C-M) as well.
    * should I add a product the formula needs to identify with a formula not with an amount

    Example. I need to type the date and the quantity in each product and the total formula will identify with the product and date and multiply together.

    Please assits
  • To post as a guest, your comment is unpublished.
    Fred · 5 years ago
    I am successful using the above formula ( =SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"
  • To post as a guest, your comment is unpublished.
    ara · 5 years ago
    Need formula for subtotal of "A" within date range. (total $ collected from "A" between 4/1 and 4/6, total $ collected from "B" between 4/1 and 4/16)
  • To post as a guest, your comment is unpublished.
    Samantha P · 5 years ago
    I have used your formula, but it only works if you type in the date, it is very static. If I make it look at a cell, it doesn't sum. What am I doing wrong.=SUMIFS(B2:B8,A2:A8,">=e4
    ",A2:A8,"
    • To post as a guest, your comment is unpublished.
      Hutchwa · 5 years ago
      I was having same issue but found another website that showed the answer:we write =SUMIFS($C$5:$C$95,$B$5:$B$95,">="&$F$5,$B$5:$B$95,"
  • To post as a guest, your comment is unpublished.
    Anon... · 5 years ago
    If you need a reference to another book/worksheet, use something like CONCATENATE(">=",'GLOBAL REPORT'!E4) as the criteria.Forsome reason this is needed.
  • To post as a guest, your comment is unpublished.
    Nadine · 5 years ago
    Which formula would you use should you wish to add certain cell values should the date be within 10 days of the current date of the spreadsheet. I.E I have two date cells, 1 being an automatic date updater when spreadsheet is opened and another date cell being that of which when an entry was made. There is over 100 lines of this and would like to add just the sales within 10 days of current date.
  • To post as a guest, your comment is unpublished.
    amcbreen · 6 years ago
    i have worksheet 1 with customer list and date of order.
    On second work sheet i have a weekly sales report with start and end range.

    i would like to look up date of order and summarize sales by week if the date range falls with the start and and end range on workbook 2. I've tried combining a sumifs, vlookup with a statement and can not resolve the calculation errors.
  • To post as a guest, your comment is unpublished.
    xlq · 7 years ago
    How do you do this when the date range is defined in another column?

    E.g. I have a main worksheet with a "week ending" column and another with expenses entered on a daily basis. How can I find and sum all expenses on or up to 7 days before each week ending date?

    =SUMIF(expenses!A2:expenses!A1024,main!$A$2&"