SUMIFS for date range (read from a cell) and text criteria not working

Hi everyone,

Would anyone of you please find time to look at this:

Please Login or Register  to view this content.

whatever I use I cannot get the date range defined by the month (1-Apr-2019) whether I use DATEVELUE, or TEXT, or just plain reference the cell N1 in the header of the column. Rest is read from another table and seems to process correctly. Basically the Transactions sheet is a huge sheet with all transactions and the table I am in is creating an overview on a month by month basis.

Thanks!

Originally Posted by Melvosh

What is the value in N1? Is it an actual date, or text formatted as a date, or something else?

Yes, it is an actual date. Are use the first day of the month so that the range can be calculated as interval between this date and the end of month function.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Thank you very much for looking into my problem. I now attach the full table, after removing a few identifiable points..

So far the function is only in the light blue cells which will then be extended to many more categories once it's working.

I have much more plans for my budgeting. It's my family budget, as you can see.

Hi

B25=-SUMPRODUCT((Transactions!$D$2:$D$48=Overview!$A25)*(MONTH(Transactions!$A$2:$A$48)=MONTH(Overview!B$1))*(YEAR(Transactions!$A$2:$A$48)=YEAR(Overview!B$1))*(Transactions!$C$2:$C$48))

copy across and down in b25

b26