Sumifs, vlookup, or date range

Jana Brits

Hey

I have some difficulty figuring out how to write a formula for my problem.

My scenario is as follows:

I have to sheets (see attached): 

1. Payment sheet
2. Cashflow sheet

The payment sheet is used to log payments to sub-contractors. There are a date column and a column for each subcontractor. The last column sums the total paid to the sub-contractors on the specific date. The sub-contractors are being paid fortnightly.

The cashflow sheet is working on a monthly bases, so I would like to sum all the amounts in the last column of the payment sheet, based on the month in which the payments were made. 

In the July 2019 column of the Cashflow sheet, I would like the sum of the amounts, in the payment sheet, paid in July.

Any ideas on how to write one formula in the cell of the cashflow sheet?

Kind regards,

Jana

Paul Newcome

You would use a formula that works something like this...

 

=SUMIFS({Payment Sheet Column to Sum}, {Payment Sheet Date Column}, AND(MONTH(@cell) = ##, YEAR(@cell) = ####))

 

You would need to use the appropriate ranges as specified and replace the #'s with the appropriate month and year numbers.