I have a list of different categories of printing and would like to enter any date on which a printout was done and how many pages were printed, thus:
| Produkt | Dato | Pris |
|---|---|---|
| A4 s/k éi | 4.5.2024 | −£ 0,41 |
| A4 s/k to | 1.5.2024: 1; 4.5.2024: 9; 5.5.2024: 4 | −£ 0,26 |
| A4 farge éi | £ 0,00 | |
| A4 farge to | 4.5.2024: 2 | −£ 10,09 |
| A4 s/k éi, fin | £ 0,00 | |
| A4 s/k to, fin | £ 0,00 | |
| A4 farge éi, fin | £ 0,00 | |
| A4 farge to, fin | £ 0,00 | |
| Bibliotek, per | £ 0,00 | |
| Blekk | £ 0,00 | |
| Tonar | £ 0,00 |
Let us call the columns A, B and C. In column A is the kind of printing cost; in column B is the date and the number of printouts; in column C is a VLOOKUP which gathers the page cost for each type of printout and multiplies that by the number in column B.
Column B is always in the format d.m.yyyy: x; where x is the number of pages. The date and month can be two digits.
What I would like to do, is find some way to extract all column B numbers that are between the colon and semicolon, add them together and multiply them by the VLOOKUP value in C. Is there any way to do that in Calc? I have so far only found one way to do this that works well with one instance: TEXTBEFORE, though I do not think that formula even exists in Calc, and in any case it only works for a single case. How can this be solved?
Main question: How can I extract each number between the colon and semicolon and sum them all?