I have a google sheet called Transactions
in which I keep track of:
Dividend payments are marked in column C
as Div
, interest payments as Int
:
---+---------------------+--------+---------+--------+
| A | B | C | D |
---+---------------------+--------+---------+--------+
1 | Date | Ticker | Action | Amount |
2 | 15.11.2023 23:00:12 | MSFT | Buy | 600 |
3 | 16.11.2023 00:00:00 | ABR | Div | 2.40 |
4 | 17.11.2023 06:22:32 | ABR | Buy | 60.50 |
5 | 17.11.2023 14:00:00 | ORCL | Div | 3.12 |
6 | 17.11.2023 16:00:00 | CWSC | Div | 0.88 |
7 | 17.11.2023 00:00:00 | UBS | Int | 23.50 |
8 | 20.11.2023 17:47:21 | ING | Int | 7.80 |
9 | 20.11.2023 18:00:00 | KO | Sell | 110.14 |
10 | 21.11.2023 22:54:11 | PEP | Div | 6.51 |
---+---------------------+--------+---------+--------+
On another sheet called Income
I want to see how much dividends and interest (Div
+ Int
) I received per day, for instance for 17.11.2023
. The expected sum here should be (3.12 + 0.88 + 23.50) 27.50
:
=sumproduct(Transactions!A:A="17.11.2023",Transactions!C:C="Div",Transactions!D:D)
This formula does not return what I need, for two reasons:
Div
rows. My question is: how can I make it select also the Int
rows?0
rows always because I am trying to match a date (17.11.2023
) with a datetime (for example 17.11.23 06:22:32
). How can I select only the date part of the values in column A
?[EDIT]
Eventually, I would run this formula in a calendar for every day of the year, like this:
You may try (works ONLY if its a valid datetime in Column_A):
=arrayformula(sumifs(Transactions!D:D, regexmatch(Transactions!C:C,"Div|Int"),true, int(Transactions!A:A),date(2023,11,17)))