Add Values in a Range Depending on Month and Other Range Values

[breenmck]

It's been quite a while since I've attempted to use VBA and I'm very rusty, so I hope my explanation below makes sense.

I have an Excel Workbook that has a Sheet, called 'Journal', that has the following Column Headers - (A) Date, (B) Transaction Type, (C) Category and (D) Amount. Both Columns B and C have a variety of Values.

What I am trying to achieve is to SUM the amounts in the Amount Column (D) depending on the Month in Column A and the Values in Columns B and C and then posting the result to a fixed Cell in another Sheet (called 'Cash Flow').

If I was expressing this in English, I would say ......
In the Journal Sheet
IF Month in Column A = January
AND Value in Column B = 'Cash' or 'Cheque'
AND Value in Column C = "Membership"
Then SUM the qualifying amounts in Column D
Post result to Cell B2 in Cash Flow Sheet

I hope this makes sense.

The version of Excel I have is 'Excel for Mac - 2011', Version 14.5

 

[Weazel]

maybe something like...

Cash Flow Sheet B2 =SUM(SUMIFS(Journal!D2:D6,Journal!C2:C6,"membership",Journal!A2:A6,"January",Journal!B2:B6,{"cash","cheque"}))

 

[breenmck]

maybe something like...

Cash Flow Sheet B2 =SUM(SUMIFS(Journal!D2:D6,Journal!C2:C6,"membership",Journal!A2:A6,"January",Journal!B2:B6,{"cash","cheque"}))

This looks like it might work It doesn't though because what I didn't tell you was that in the Date column (A), the date is shown in the format 'dd/mm/yyyy'. So I'm looking for a number for the month - i.e. '1' rather than "January.

 

[Weazel]

ok, maybe something like


=SUMPRODUCT(SIGN(Journal!B2:B6={"cash","cheque"})*(Journal!C2:C6="membership")*(TEXT(Journal!A2:A6,"mmmm")="January")*(Journal!D2:D6))

I think if you had the flexibility to add a month column the sumifs would probably be better

 

[breenmck]

ok, maybe something like


=SUMPRODUCT(SIGN(Journal!B2:B6={"cash","cheque"})*(Journal!C2:C6="membership")*(TEXT(Journal!A2:A6,"mmmm")="January")*(Journal!D2:D6))

Actually, you're first solution set me to thinking and I've come up with a solution that appears to work. It's based on yours, but not quite so elegant. What I've done is I've added to new Cells to the 'Cash Flow' Sheet called 'Start Date' and 'End Date'. So for January the Start Date is 01/01/2015 and the End Date is 31/01/2015. I've then used these dates in the following formula:

=SUM(SUMIFS(Journal!E2:E400,Journal!A2:A400,">="&D2,Journal!A2:A400,">="&E2,Journal!C2:C400,"Membership",Journal!B2:B400,"Cash - Admin")+SUMIFS(Journal!E2:E400,Journal!A2:A400,">="&D2,Journal!A2:A400,">="&E2,Journal!C2:C400,"Membership",Journal!B2:B400,"Cheque - Admin"))

'D2' and 'E2' being the Start and End Dates in the Cash Flow Sheet.

Thanks for your help in getting me there!

 

[Weazel]

glad you got it figured out

 

[Jonmo1]

Wouldn't that second criteria need to be "<=" &E2 ?

Anyway, you can apply the 'elegant' part of weazle's original formula like this

=SUM(SUMIFS(Journal!E2:E400,Journal!A2:A400,">="&D2,Journal!A2:A400,"<="&E2,Journal!C2:C400,"Membership",Journal!B2:B400,{"Cash - Admin","Cheque - Admin"}))

 

[breenmck]

Wouldn't that second criteria need to be "<=" &E2 ?

Anyway, you can apply the 'elegant' part of weazle's original formula like this

=SUM(SUMIFS(Journal!E2:E400,Journal!A2:A400,">="&D2,Journal!A2:A400,"<="&E2,Journal!C2:C400,"Membership",Journal!B2:B400,{"Cash - Admin","Cheque - Admin"}))

You're right about the "<=". Thanks. Haven't tried the 'elegant' solution yet, but again, thanks.