Help Request - Nested formulas in a SUMIFS formula

[Demonsguile]

Every month I get a ledger from my property management company detailing what expenses were incurred. Unfortunately, it's not setup to be easily digestible. So, I've decided to parse out the data into something where I can more easily understand it.

I'm trying to write a formula which searches for a few conditions and sums the values it finds. Unfortunately, when it comes to yard maintenance activities, the ledger displays "Yard - 10/28". As the date will change each time, I want to use the first six (6) characters or "Yard -" in my formula.

Here is what I came up with:
=SUMIFS('Ledger - Details'!$G:$G, 'Ledger - Details'!$F:$F, $B$24, 'Ledger - Details'!$C:$C, "NORTHERNDOVE-", 'Ledger - Details'!$H:$H, E$1)
The relevant section is 'Ledger - Details'!$F:$F, $B$24 where $B$24 references "Yard -".

Unfortunately, when I try to limit it to the six characters I want, I get an error. The formula that errors is this:
=SUMIFS('Ledger - Details'!$G:$G, LEFT('Ledger - Details'!$F:$F, 6), $B$24, 'Ledger - Details'!$C:$C, "NORTHERNDOVE-", 'Ledger - Details'!$H:$H, E$1)

Any help would be appreciated.

Thanks,
DG

 

[Peter_SSs]

Change to SUMPRODUCT instead of SUMIFS and try something like this.
I usually try to avoid whole column references. I doubt you are using anything like the 1 million+ rows available so choose something where I have $2000 that is big enough to cover your likely data but presumably well short of the available row limit.

=SUMPRODUCT(--('Ledger - Details'!$C$1:$C$2000="NORTHERNDOVE-"),--(LEFT('Ledger - Details'!$F$1:$F$2000,6)=$B$24),--('Ledger - Details'!$H$1:$H$2000=E$1),'Ledger - Details'!$G$1:$G$2000)