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 each product qualifies you to earn a profit from the sale if you make the sale less than or equal to date as earning the profit.

Second table:

Contains the total sales of those products on different dates.

what i need is to :

Calculate the quantity of all products sold less than or equal to day as the profit earning date.

 

i already attracted example for what i need exactly .

 

Thank you

 

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.

     

4 Replies

  • 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.

         

Resources