I was wondering if I could get some help with this formula…
I’m trying to make an expenses tracking sheet and calculate the amount of money spent this month.(bottom right corner)
The formula works but if I add another entry into the sheet I have to then manually change the formula as well, and I was wondering if there’s a way to automate it?
If I change A4:A6 in the formula to A:A, I get a #!VALUE error.
Thank you in advance!
The arrays supplied for SUMPRODUCT() need to have corresponding dimensions. Also the argument to MONTH() must be a number. You have labels in column A, which MONTH(A:A) evaluates on, and A:A dimensions do not correspond well to D4:D6. Each of those mistakes will return the #VALUE error, so you need two changes.
Try =SUMPRODUCT(MONTH(N(A:A))=MONTH(NOW());D:D)
Better: try =SUMPRODUCT(IFERROR(MONTH(A:A);0)=MONTH(NOW());D:D)
Thank you, this worked very well!
Hello,
That’s why there are people preferring/recommending to do summary calculations above data, this way you could use ranges like A4:A1048576 and D4:D1048576. If you want it below: Use A4:A7 and D4:D7 and always add new lines using the following procedure:
Right click -> Insert Row BelowThis will adapt your formula automatically.
Hope that helps.
I tried this also and it worked. Thank you.
Awareness of the range you calculate on is usually better than “catch-all” formulas. While my answer perhaps better explains the causes of your formula error, @anon73440385’s advice is probably a better path to follow.