I have a table with project information and I need to sum up the total turnover for the present year if certain criterias are met.
I have criterias for selecting projects using sumproduct:
Criteria 1: Status ="Ordre"
Criteria 2: Department = "B"
Criteria 3: Order date < present year
Then I have If statements for calculating the turnover for each of the projects I have found using the sumproduct formula.
Basically I'm finding the project workdays in 2025 and dividing it by the total number of workdays for the project
Criteria 1: If start<2025 and finish<2025 then 0
Criteria 2: If start>2025 and finish>2025 then 0
Criteria 3: If start=2025 and finish=2025 then Order sum
Criteria 4: If start<2025 and finish=2025 then (order sum x workdays(01-01-2025; finish))/workdays(start;finish)
Criteria 5: If start=2025 and finish>2025 then (order sum x workdays(start; 31-12-2025))/workdays(start;finish)
Criteria 6: If start<2025 and finish>2025 then (order sum x workdays(01-01-2025; 31-12-2025))/workdays(start;finish)
I know how to write this very long if statement and I know how to write the sumproduct statement, but I don't know how to combine them or if sumproduct is the right way to go with this.
I have considered creating an extra column in my table for the if statement and then using sumproduct on this column, but the table is refreshed every day with new project data, and I'm not sure the new column will calculate new lines.
I'm using Excel365
I've attached a sample table for testing if needed.
Re: Using if formula for each instance in sumproduct
I created a function called Turnover2025 using VBA, the code of which is below. You will also see the attached file where you can see the output of the calculations.
PHP Code:
Function Turnover2025(startDate As Date, finishDate As Date, orderSum As Double) As Double
Dim ws2025Start As Date, ws2025Finish As Date
Dim totWorkdays As Long, partWorkdays As Long
Re: Using if formula for each instance in sumproduct
Thank you all for your answers but it's not what I'm looking for.
I know how to write the if fomula containing the networkday formula. That's not my problem.
But I don't know how to combine it with the sumproduct formula.
I want one cell containing the total turnover for 2025 for all projects in department B, where the turnover for each project is calculated using the if formula
basically:
find all projects in department B
calculate 2025 turnover for each project
sum up 2025 turnover for department B
I can work around it by adding an extra column to my table calculating the if formula for each project and then do a sumproduct on this column.
But I was hoping that I didn't have to add the extra column.
I was also hoping not to use vba for this.
It might not be sumproduct that's the best way, but some other combination of the if formula and a look up formula.
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
Posts
90,215
Re: Using if formula for each instance in sumproduct
I don't see where you shared the IF formula that you say you've already created.
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy. You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests. Forum Rules (updated August 2023): please read them here.
It's in danish but to help translate
HVIS=IF
OG=AND
ÅR=YEAR
IDAG=TODAY
ANTAL.ARBEJDSDAGE=NETWORKDAYS
DATO=DATE
Then in another sheet I do my sumproduct on the helper column
SUMPRODUCT((Tabel_SharePoint[omsætning indeværende år])*(ÅR(Tabel_SharePoint[Ordredato])<ÅR(IDAG()))*(Tabel_SharePoint[Afdelings chef]='One Pager'!$B$6))/1000
For now that works, but just for a learning point of view it would still be nice to know if the IF formula can be integrated into the SUMPRODUCT formula
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
Posts
90,215
Re: Using if formula for each instance in sumproduct
Better to provide a sample workbook, as formulae are automatically translated therein. Also, if you need further assistance, then you need to provide a workbook.
Re: Using if formula for each instance in sumproduct
Danish in formulas!? For helvede mand!
As a fellow scandi, the first thing I do on a new computer is set the Excel language to English, so much easier to search for help.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by JohnTopley
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left 
Bookmarks