sort and sum costs by month and by type

**huginori** · 01-23-2025, 07:14 AM

Hello everyone,

I have 1000+ items expense list across 15 different cost types and all different payment dates. I need to show a monthly sum of the different cost types. I have a feeling that it is an index/match formula but I always end up getting confused with that. I also don't know if it is possible to sum expenses in a month as I have specific dates in the cells. Please find attached a simplified version of the expense list (well, let's hope I can actually attach it).
I am so grateful for all you geniuses in advance.

Many thanks in advance

**MiNd_HuNT3r** · 01-23-2025, 07:56 AM

you should use the Month & Filter functions for this, so use this formula:

PHP Code:


=IFERROR(SUM(FILTER($C$2:$C$22,($A$2:$A$22=$E2)*(MONTH($B$2:$B$22)=MONTH(F$1)))),"")

And if you want the reports to be longer than a one-year period, you can use the following formula:

PHP Code:


=IFERROR(SUM(FILTER($C$2:$C$23,($A$2:$A$23=$E2)*(MONTH($B$2:$B$23)=MONTH(F$1))*(YEAR($B$2:$B$23)=YEAR(F$1)))),"")

**huginori** · 01-23-2025, 01:04 PM

Thank you very much MiNd_HuNT3r!

This works perfectly with the example I've given. I have tried to apply to my actual sheet but returned blanks only.
I added an extra sheet to demonstrate what I did (V2)- I had to change the column references, but I don't see what I messed up. (I deleted sensitive info and hidden irrelevant columns)
The cost headings are copy pasted (originally from a dropdown list in both the source table and the output table) so there should not be a difference.
I have played around with the date formats to make them the same in both the source table and the output table, but didn't seem to make a difference.

Could you please look at my sheet and see where I made a mistake? Thank you very much in advance for your help!

**JohnTopley** · 01-23-2025, 01:46 PM

Check out your date fields (column H) where there invalid values.

**MiNd_HuNT3r** · 01-23-2025, 02:07 PM

Your data in column Defrayal Date has problem and I had to change the formula and it worked, but some cells in column Defrayal Date were invalid, so They were not included in the calculations, By the way some cells don't have date. I recognized it for you under the table.

PHP Code:


=IFERROR(SUMIFS($O$2:$O$300,$D$2:$D$300,$S2,$H$2:$H$300,">="&DATE(YEAR(T$1),MONTH(T$1),1),$H$2:$H$300,"<"&DATE(YEAR(T$1),MONTH(T$1)+1,1)),"")

**huginori** · 01-24-2025, 07:22 AM

Thank you very much MiNd_HuNT3r!
Yes, some of the expenses have not been paid yet so they do not need to be added just yet. I have fixes those few entries where I didn't have a valid date format (typos). It works now, I just need to find why my totals don't add up (it's going to be a silly mistake somewhere I made and I don't want to bother you with it).
Can you tell me what was the problem with the data in "Defrayal date" column? I am curious to learn.

Thank you very much for you thorough and quick responses!

**JohnTopley** · 01-24-2025, 07:38 AM

Check rows 54,60,66,121

**MiNd_HuNT3r** · 01-24-2025, 07:48 AM

Welcome. I'm glad to hear that.
The FILTER function can't properly handle date data for month and year comparisons unless all date values are correctly recognized and logical calculations are applied in the FILTER conditions. If there's even one invalid date or a value not recognized as a date within the range $H$2:$H$300, the FILTER function will throw an error. Additionally, the conditional multiplication (*) for combining conditions in FILTER can cause errors in some cases, especially when TRUE/FALSE values are mixed with non-numeric data.

**avk** · 01-24-2025, 08:16 AM

"F2"

Formula:

Please Login or Register  to view this content.

Copy paste across.

**alansidman** · 01-24-2025, 12:21 PM

Why not a pivot table

**huginori** · 01-24-2025, 05:50 PM

Thank you very much All, especially MiNd_HuNT3r

I've changed the date format into "long date" and that highlighted where the (to me) less obvious issues were.
Now it all balances out (sum or source table equals the sum of the output) and it is BEAUTIFUL! Saved me a few days of having to do this manually (as there will be another couple of thousands of rows).

Thank you very much again for all your brilliant minds!

Have a great weekend,

Nora

sort and sum costs by month and by type

LinkBack

Thread Tools

Rate This Thread

Display

sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Re: sort and sum costs by month and by type

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Can't apportion multi-month costs by month

Costs per Month

[SOLVED] Sum costs for a month and year using sumifs

Develop Macros to Display Costs for Each Month (VBA)

Need help with calculating project costs with the costs different each month.

Combine costs per month

storage costs that vary in weight each month

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions