If statement using "between"

[stephen.m.brown]

I have a spreadsheet (dummy attached).

This has 4 tabs:

Summary - this is a lookup from other tabs and works fine
List of apprentices - this is a list of data and is fine
Payments in (Levy) - again OK as list

My query is on the Payments out (Training) tab.

I would like column B to be a total SUM of the "monthly payment column" on the list of apprentices tab IF the month in Column A is between the start and end month on the list of apprentices tab.

I would like column B to be a total SUM of the "End point payments" column from list of apprentices tab IF the month in Column A is the same as the end month on the list of apprentice tab

Hope this makes sense and someone might be able to help!!!

[AliGW]

In a sample workbook, you also need to show your expected outcomes (manually calculated and entered). Thanks!

[stephen.m.brown]

Sorry - uploaded attachment showing what I would expect on the Payments out tab

Thanks

[Pete_UK]

You can use this formula in B2:

=SUMIFS('List of Apprentices'!$J:$J,'List of Apprentices'!$G:$G,"<="&$A2,'List of Apprentices'!$I:$I,">"&$A2)

and this one in C2:

=SUMIFS('List of Apprentices'!$K:$K,'List of Apprentices'!$I:$I,$A2)

then copy both formulae down.

Note that these work because all your dates relate to the first of the month - you might need something more elaborate if your real dates are spread across a month.

Hope this helps.

Pete

[stephen.m.brown]

Excellent - thanks for help. Seems to have worked

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JohnTopley]

In B2

=SUMIFS('List of Apprentices'!$J$2:$J$3,'List of Apprentices'!$G$2:$G$3,"<=" &'Payments out (Training)'!$A2,'List of Apprentices'!$I$2:$I$3,">="&'Payments out (Training)'!$A2)

in C2

=SUMIFS('List of Apprentices'!$K$2:$K$3,'List of Apprentices'!$I$2:$I$3,"="&EOMONTH('Payments out (Training)'!$A2,0))

Change I2 to 31/01/2018