Forum Discussion
Calculate based on number between text and search on text
Hello everyone,
I have a question, I'm not sure if it is possible to do so, but I'll shoot my shot in asking.
I added the excel-file. I already asked ChatGPT but, same as last time, the answers it gives me aren't compatible with either the version I use or it isn't what I'm asking for.
So, here's the deal:
In column G under "Berekeningen" you'll see the next cells:
- Totaal aantal interventies
- Gemiddeld aantal interventies per week
- Totaal aantal installaties
- Gemiddeld aantal installaties per week
I would need the following:
- Totaal aantal interventies: In the range filled in using conditional formatting, search for where the words "AANWEZIG" and "INI" are. There is a number written between both words, a sum must be made of all these numbers.
- Gemiddeld aantal interventies per week: Calculate the average per week of the above, so in other words: search for where the words "AANWEZIG" and "INI" are. There is a number written between both words, a sum must be made of all these numbers and the total of this must be divided by the number of weeks, in this case 9 weeks.
- Totaal aantal installaties: In the range filled in using conditional formatting, search for where the words "AANWEZIG" and "INS" are. There is a number written between both words, a sum must be made of all these numbers.
- Gemiddeld aantal installaties: Calculate the average per week of the above, so in other words: search for where the words "AANWEZIG" and "INS" are. There is a number written between both words, a sum must be made of all these numbers and the total of this must be divided by the number of weeks, in this case 9 weeks.
This is the range using conditional formatting:
=$B$3:$F$8;$B$10:$F$15;$B$17:$F$22;$B$24:$F$29;$B$31:$F$36;$B$38:$F$43;$B$45:$F$50;$B$52:$F$57;$B$59:$F$64
For info, my Excel only accepts European (Belgian-written) formulas. The version used is version 2407 (build 17830.20138) and is included in the Microsoft 365-apps for businesses.
Here's an example of a formula ChatGPT gave me:
Totaal aantal interventies:
=SOMPRODUCT(--(ISGETAL(VIND.SPEC("AANWEZIG"; B2:F1000))), --(ISGETAL(VIND.SPEC("INI"; B2:F1000))), --(DEEL(B2:F1000; VIND.SPEC("AANWEZIG"; B2:F1000)+9; VIND.SPEC("INI"; B2:F1000)-VIND.SPEC("AANWEZIG"; B2:F1000)-9)))
Gemiddeld aantal interventies per week:
=ALS(AANTAL.ALS.UNIEK(WEEKNUM(B1:F1))=0; 0; (Totaal_aantal_interventies)/AANTAL.ALS.UNIEK(WEEKNUM(B1:F1)))
Due to which I received the following faults: "#WAARDE!" and "#NAME?"
I even tried with "help columns" but it didn't work either. I also tried using VBA with a code ChatGPT gave me, but sadly I lost it so I can't paste it here.
Can anyone tell me if what I ask for is possible, and if so, how to obtain it? I would need to copy paste the formula for another range (the next two following months, which I haven't created in the file yet).
Thanks in advance!
Since I'm not very good with Belgian formulas, here is an example with VBA.
Code is not tested, please backup your file beforehand.
Sub CalculateInterventionsAndInstallations() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim startPos As Long Dim endPos As Long Dim interventionSum As Double Dim installationSum As Double Dim numWeeks As Long Dim tempValue As Double Dim textToSearch As String ' Set your worksheet and range here Set ws = ThisWorkbook.Sheets("Berekeningen") Set rng = ws.Range("B3:F64") ' Adjust this range as needed ' Initialize sums interventionSum = 0 installationSum = 0 numWeeks = 9 ' Set the number of weeks ' Loop through each cell in the range For Each cell In rng If Not IsEmpty(cell.Value) Then ' Search and sum for interventions ("AANWEZIG" and "INI") textToSearch = cell.Value startPos = InStr(1, textToSearch, "AANWEZIG", vbTextCompare) If startPos > 0 Then endPos = InStr(startPos, textToSearch, "INI", vbTextCompare) If endPos > startPos Then tempValue = Mid(textToSearch, startPos + 9, endPos - startPos - 9) If IsNumeric(tempValue) Then interventionSum = interventionSum + CDbl(tempValue) End If End If End If ' Search and sum for installations ("AANWEZIG" and "INS") startPos = InStr(1, textToSearch, "AANWEZIG", vbTextCompare) If startPos > 0 Then endPos = InStr(startPos, textToSearch, "INS", vbTextCompare) If endPos > startPos Then tempValue = Mid(textToSearch, startPos + 9, endPos - startPos - 9) If IsNumeric(tempValue) Then installationSum = installationSum + CDbl(tempValue) End If End If End If End If Next cell ' Output results to specific cells ws.Range("G2").Value = interventionSum ws.Range("G3").Value = interventionSum / numWeeks ws.Range("G4").Value = installationSum ws.Range("G5").Value = installationSum / numWeeks MsgBox "Calculations completed successfully!" End SubI hope this helps you with your project.
Since I'm not very good with Belgian formulas, here is an example with VBA.
Code is not tested, please backup your file beforehand.
Sub CalculateInterventionsAndInstallations() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim startPos As Long Dim endPos As Long Dim interventionSum As Double Dim installationSum As Double Dim numWeeks As Long Dim tempValue As Double Dim textToSearch As String ' Set your worksheet and range here Set ws = ThisWorkbook.Sheets("Berekeningen") Set rng = ws.Range("B3:F64") ' Adjust this range as needed ' Initialize sums interventionSum = 0 installationSum = 0 numWeeks = 9 ' Set the number of weeks ' Loop through each cell in the range For Each cell In rng If Not IsEmpty(cell.Value) Then ' Search and sum for interventions ("AANWEZIG" and "INI") textToSearch = cell.Value startPos = InStr(1, textToSearch, "AANWEZIG", vbTextCompare) If startPos > 0 Then endPos = InStr(startPos, textToSearch, "INI", vbTextCompare) If endPos > startPos Then tempValue = Mid(textToSearch, startPos + 9, endPos - startPos - 9) If IsNumeric(tempValue) Then interventionSum = interventionSum + CDbl(tempValue) End If End If End If ' Search and sum for installations ("AANWEZIG" and "INS") startPos = InStr(1, textToSearch, "AANWEZIG", vbTextCompare) If startPos > 0 Then endPos = InStr(startPos, textToSearch, "INS", vbTextCompare) If endPos > startPos Then tempValue = Mid(textToSearch, startPos + 9, endPos - startPos - 9) If IsNumeric(tempValue) Then installationSum = installationSum + CDbl(tempValue) End If End If End If End If Next cell ' Output results to specific cells ws.Range("G2").Value = interventionSum ws.Range("G3").Value = interventionSum / numWeeks ws.Range("G4").Value = installationSum ws.Range("G5").Value = installationSum / numWeeks MsgBox "Calculations completed successfully!" End SubI hope this helps you with your project.
- Thank you!
Without getting involved too much with the Dutch/Flemish here are some 365 formulas that might be useful
= SUM(VALUE(TOCOL(REGEXEXTRACT(source, "\d+(?=\sINI)"),3))) = SUM(VALUE(TOCOL(REGEXEXTRACT(source, "\d+(?=\sINS)"),3))) = ROWS(TOCOL(REGEXEXTRACT(source, "[A-Z]"), 3)) = SUM(SIGN(ISTEXT(source)))where 'source' refers to the entire array of tables. The regular expressions are looking for any numbers followed by one space and the strings "INI" and "INS" respectively. If you use 365 the formulas should translate OK.
lauradelfosse You've already accepted a VBA solution that does what you asked for. Provided that you are using a modern Excel version and are still interested in a formula based solution, have a look at the attached file, where I entered some formulas.
For the total number of interventions I used (in English):
=SUM(VALUE(IFERROR(TEXTBEFORE(TEXTAFTER(IF(SIGN(SEARCH("AANWEZIG*INI",B3:F64)),B3:F64,""), " "), " "),0)))and for the installations:
=SUM(VALUE(IFERROR(TEXTBEFORE(TEXTAFTER(IF(SIGN(SEARCH("AANWEZIG*INS",B3:F64)),B3:F64,""), " "), " "),0)))When you open the attached file the formulas will translate into Dutch automatically.
