I have two (2) worksheets titled:

• Spending Journal
• Spending Analysis

I am attempting to sum all the values in the 'Spending Journal'!E:E spreadsheet that meet both the following rules:

• 'Spending Journal'!D:D matches with 'Spending Analysis'!B4
• 'Spending Journal'!A:A is between a date range between >='Spending Analysis'!C3 & <'Spending Analysis'!D3

All date cells dates have been formatted as YYYY-MM-DD

The formulas I have used with no luck are the following:

• =SUMIFS('Spending Journal'!E:E,'Spending Journal'!A:A,"'>='Spending Analysis'!C3",'Spending Journal'!A:A,"<'Spending Analysis'!D3",'Spending Journal'!D:D,'Spending Analysis'!B4)
• =SUM(VLOOKUP(B22,'Spending Journal'!D:E,'Spending Journal'!E:E),FALSE)
• =SUMIF('Spending Journal'!A4:E10000,'Spending Analysis'!E3>'Spending Journal'!A4:E10000>'Spending Analysis'!D3,XLOOKUP('Spending Analysis'!B22,'Spending Journal'!D:D,'Spending Journal'!E:E,"Error",0))

I have been trying to think on this for a couple days so any help would be amazing, even if it is pointing me in the correct direction.

Thanks in Advance!

CodePudding user response：

In spending analysis cell 'C4' paste this:

=SUMIFS('Spending Journal'!$E:$E,'Spending Journal'!$A:$A,"<="&EOMONTH('Spending Analysis'!C$3,0),'Spending Journal'!$A:$A,">="&'Spending Analysis'!C$3,'Spending Journal'!$D:$D,'Spending Analysis'!$B4)

and it should be able to copy and fill the rest of the spending analysis cells.

CodePudding user response：

I'm a software developer working on a product that generates excel formulas.

According to my software, this might be appropriate: =SUMIFS('Spending Journal'!E:E, 'Spending Journal'!D:D, 'Spending Analysis'!B4, 'Spending Journal'!A:A, ">="&TEXT('Spending Analysis'!C3, "YYYY-MM-DD"), 'Spending Journal'!A:A, "<"&TEXT('Spending Analysis'!D3, "YYYY-MM-DD"))

Let me know if this is at all accurate.