SOLVED

how to sum different number within dates range ?

Copper Contributor

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 .

Image 01-08-1443 AH at 1.28 AM.jpg

 

Thank you

 

4 Replies
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.

hi@Grahmfs13 

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

 

Screen Shot 1443-08-01 at 5.15.06 AM.png

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

i hope you understand my idea

best response confirmed by nalnofal (Copper Contributor)
Solution

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

Screenshot 2022-03-04 at 07.23.17.png

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.

 

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.

 

1 best response

Accepted Solutions
best response confirmed by nalnofal (Copper Contributor)
Solution

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

Screenshot 2022-03-04 at 07.23.17.png

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.

 

View solution in original post