Hi,
My basic SUMIFS formula works fine until I try to introduce the LEFT function so I tried wrapping the SUMIFS formula in the SUM function hoping that would help but unfortunately it doesn't, any ideas as to how to use the two functions together?
=sum(SUMIFS(Statements[Paid out], Statements[Account], "Joint",left(Statements[Transaction type],6), "Direct",Statements[Date], ">="&XLOOKUP(MAXIFS(Statements[Date], Statements[Account], "Joint", left(Statements[Transaction type],6),"Direct") & "Joint",Statements[Date] & Statements[Account], Statements[Transaction type], [Date])))
Thanks,
David
left makes the result text - try adding a zero to make the result of left a number
=Left(a1,6)+0
Thanks but this doesn't work either.
Hi David,
Please supply your file so we can see what data the formula is using. Really hard to debug it without this.
Also please state what result you are expecting.
Regards
Phil
I thought had added an upload but just done again - I've stripped the data back but the date element of the formula works well for picking up categorised items (column and referencing also deleted) where there are multiple inflows or outflows of the same category on the same day of the most recent month - the date element is needed but can be ignored for the purposes of the attached, the focus is really on combining SUMIFS and LEFT, thanks.
You cannot use functions on the ranges in a SUMIFS formula as that would return an array, not a range, and the function will not accept anything other than a range. However you can use wildcards, so you'd simply use the range as normal and "Direct*" as the criterion.
Thank you, simples :).
Glad we could help. 🙂
