Forum Discussion

nalnofal's avatar
nalnofal
Copper Contributor
Mar 03, 2022
Solved

how to sum different number within dates range ?

Hi everyone  I have issue to find the right formula to (sum Product total Number if less than or equal to specific date) I have two tables: The first is a profit table for different products, and ...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 04, 2022

    nalnofal Many of the dates in the Total Sales table are in fact texts that look like dates. That's why they do not meet the criteria set in the SUMIFS formula. You can check that using the ISNUMBER function. change the text dates to real dates an it will work.

    Select all dates. On the Data ribbon select Text to columns and just press Finish without changing any of the settings. Done that in the attached file.

     

Grahmfs13's avatar
Grahmfs13
Former Employee
Mar 03, 2022
Hi, seems you may be able to use a IF function to help determine the difference of date and retrieve whatever value you want if true (I used difference of QTY as example). For example =IF(D9=K9,F9-L9,"Date Greater"). You will also want to make sure both date columns are in the same date format for this if function to work before running. If just need total QTY then using F9 as your True statement will work too.
  • nalnofal's avatar
    nalnofal
    Copper Contributor
    Mar 04, 2022

    hiGrahmfs13 

    Let me explain to you what i really want .

    I use SUMIFS Formula to calculate total QTY from Due Date until today date :

    =SUMIFS(Total_sales[QTY],Total_sales[Product Name],[@[Product Name]],Total_sales[Date of sale],"<="&[@[Due date]],Total_sales[Date of sale],"<="&$F$4)

    But as you see I can’t get exact Total QTY

     

    for example I want to find total QTY for Ipad cover if Date of sale <= Due Date

    i hope you understand my idea

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Mar 04, 2022

      nalnofal Many of the dates in the Total Sales table are in fact texts that look like dates. That's why they do not meet the criteria set in the SUMIFS formula. You can check that using the ISNUMBER function. change the text dates to real dates an it will work.

      Select all dates. On the Data ribbon select Text to columns and just press Finish without changing any of the settings. Done that in the attached file.

       

      • nalnofal's avatar
        nalnofal
        Copper Contributor
        Mar 05, 2022

        Thank you very much Riny_van_Eekelen 

        your solution is very helpful , i review my original Data and i find many dates value is text even i change the format to dates .

        So that the issue not in my formula and all my calculation was perfect.

        I will keep in mind to use ISNUMBER function for all my dates value.

         

Resources