Minimize formula too long for a cell

[somar]

Hello
Attached is my sheet sample with also more details questions.
I've been doing my expenses on excel sheet entering the data manually.
I want to copy and paste my bank statement and do it automatically.


My simple question is: How do I simplified a formula storing a repetitive function and variable data ?

Is it possible to save the part of the formula on a cell a make reference to it

[Glenn Kennedy]

I simply CANNOT use RC notation... so I went back to conventional notation. This is an array formula in your Excel version:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(L27:L29),D11:D32))),ROW(L27:L29)^0)>0)*F11:F32)

Feel free to reset to RC notation!!

[Glenn Kennedy]

Ahhh the usual problem. US dates versus rest-of-world dates. So, I reformatted dates into MDY format and added a filter to count by month. See revised file. I changed one date for testing.

D4, entered as an array formula, copied across.

=SUMPRODUCT(--(MMULT((--(ISNUMBER(SEARCH(TRANSPOSE($L$27:$L$29),$D$11:$D$32)))*(TEXT($C$11:$C$32,"mmm")=D2)),ROW($L$27:$L$29)^0)>0)*$F$11:$F$32)

[somar]

Thank you Glenn. Problem solved

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.