Mar 03 2022 03:03 PM
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
Mar 03 2022 03:53 PM
Mar 03 2022 06:26 PM
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
Mar 03 2022 10:28 PM - edited Mar 03 2022 10:29 PM
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.
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.
Mar 05 2022 10:45 AM
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.
Mar 03 2022 10:28 PM - edited Mar 03 2022 10:29 PM
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.
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.