Help with SUMIFS

Topic

New Reply

I have a spreadsheet where one tab is a list of transactions over the year. i want to sum the amounts of each transaction by month, but I’m having problems working out how to do this. I’m using SUMIFS to attempt this (having used it successfully for a different sum), but it’s not obvious to me how to do it by month.
Some sample data
A B C D E
29/04/2021 Receipt Etrans Subscriptions 87.50
30/04/2021 Receipt Etrans Subscriptions 50.00
04/05/2021 Receipt Etrans Subscriptions 125.00
05/05/2021 Receipt Etrans Speakers 50.00

Because the data is being added to I’m using the E:E to reference the cells
I can do it easily if I just sum by column D (so two sums) but how do I separate out the (in this case) two months?
The one I’m basing it on is like this
=SUMIFS(‘Ledger – main’!E:E,’Ledger – main’!B:B,”receipt”,’Ledger – main’!D:D,A5)
Where ‘Ledger – main’ is the sheet the data’s on. A5 is on the same sheet as the formula and has a helper column (A5) matching the required item in Column D.
The sheet this is going on has cells with the month numbers, so July is 7 etc.

Eliminate spare time: start programming PowerShell

Reply | Quote

Replies

Access,

From my experimentation it looks like you’ll need a helper column on your Transaction sheet that has only the month number. You can generate that number using the formula =Month($A1) and fill it down. from there you can use the following formula to generate your totals: =SUMIF(F:F,$H2,E:E) you can also fill that one down.

Example:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

access-mdb

Reply | Quote

Thanks RG, not quite what I was after as I need to do a SUMIFS as the item column needs to be in there as well. I’ll have a look later (visitors coming) and see if I can rejig your expression for the SUMIFS function.

Eliminate spare time: start programming PowerShell

Reply | Quote

Access,

Ok, here’s one w/o a helper column:
=SUMIFS(E:E,A:A,”>=”&$H2&”/1/2021″,A:A,”<"&$H3&"/1/2021") The only caveat here is that you'll have to adjust the December formula after you do the fill down. =SUMIFS(E:E,A:A,">=”&$H13&”/1/2021″,A:A,“<1/1/2022")

Update:
Using EDate as shown by BTBS below you can eliminate the need for editing the final formula, e.g. =SUMIFS(E:E,A:A,”>=”&$H2&”/1/2021″,A:A,”<"&EDATE($H2&"/1/2021",1))

Sample:

Note: the highlighted line is not included!

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

@Access-mdb,

Another way to do it is as per attached. ‘Ledger – main’ is as you have described, ‘Summary’ is the month by month summary. On the summary sheet:

A2: 01/04/2021
A3: =EDATE(A2,1)
A4:A5: Fill down A3

B2: =SUMIFS(‘Ledger – main’!E:E,’Ledger – main’!A:A,”>=”&A2,’Ledger – main’!A:A,”<“&EDATE(A2,1))
B3 onwards: fill down B2

Summary column A is formatted as “dd/yyyy” but can be formatted however you want.

The two date tests (“>=”&A2) and (“<“&EDATE(A2,1)) make sure the date falls in the month specified.

Note that all quotes should be straight quotes. I tried using the ‘code’ tags but it ran all the text together.

Hope that helps.

Reply | Quote

A2: 01/04/2021
A3: =EDATE(A2,1)
A4:A5: Fill down A3

B2: =SUMIFS('Ledger – main'!E:E,'Ledger – main'!A:A,">="&A2,'Ledger – main'!A:A,"<"&EDATE(A2,1))
B3 onwards: fill down B2

cheers, Paul

Reply | Quote

Hi Access.

I think you can pivot the data to get your desired output. Echoing with @btbs, you can follow the formula. However, it would be better if you share the file as attachment.

Shazzad

Reply | Quote

Thanks everyone, I will have a look at each and see what I can garner from this. I’ve never used Excel for more than basic stuff – after I retired started using VBA (because I used VBA on Access). Now I’m using what are for me esoteric functions – XLOOKUP, SUMIF(S) and I’m enjoying the challenge! But pivot tables look like ‘here be dragons’ to me!

Eliminate spare time: start programming PowerShell

Reply | Quote

OK, thanks to all for your comments. I had to look up EDATE and once I understood how it worked, I was able to get exactly what I wanted (with some trial and error!). It adds another criteria (criterion?) but I’ve got 127 to play with!

For your edification this is the formula

=SUMIFS(Ledger!E:E,Ledger!A:A,”>=”&B2,Ledger!A:A,”<“&EDATE(B2,1),Ledger!D:D,A3)

B2 (C2, D2 etc) is the 1st of the required month and A3 (A4, A5, A6 etc) is the item (subscriptions etc). This pretty much matches the cash flow tab I’m trying to automate.

You lot rock !

Eliminate spare time: start programming PowerShell

Reply | Quote