Struggling with sum product/month function

[Rashie]

Hi All,

Hoping someone can help.

I've got a sheet for 2016 I'm hoping to have ready by Jan 1st and I can't get my ongoing totals to work.

At the top of my sheet I've got a summary of all months for a board report and here's an example specifically for January for the example but this will run all year.

[TABLE="width: 50"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Jan 2016[/TD]
[/TR]
[TR]
[TD]Bookings total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly Spend[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Profit/Loss Monthly[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now I want this summary at the top of my sheet to keep a running total of how we do month by month.

[TABLE="width: 70, align: left"]
<tbody>[TR]
[TD="align: center"]Date Of booking[/TD]
[TD="align: center"]Total Spend[/TD]
[TD="align: center"]Passengers Booked[/TD]
[TD="align: center"]Profit/Loss[/TD]
[TD="align: center"]Departure[/TD]
[TD="align: center"]Destination[/TD]
[TD="align: center"]Card Used[/TD]
[/TR]
[TR]
[TD="align: center"]10 Jan 16[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10 mar 16
[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10 jul 16[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]











Now is it possible to use the sum product/month function to keep track of my table to add up the totals for each month for my summary? Any help would be appreciated

Thanks,

Rashie.

 

[Aladin Akyurek]

Hi All,

Thanks for your help earlier, i've worked it out myself now.

F2 value is : =SUMPRODUCT(--(TEXT(A7:A9999,"mmmyyyy")=TEXT(F1,"mmmyyyy")),M7:M9999)

thanks again,

Rashie.

Wonderful. By the way, it's exactly equivalent of:

=SUMPRODUCT(--(A7:A9999-DAY(A7:A9999)+1=F1-DAY(F1)+1),M7:M9999)

If F1 is a first day date...

=SUMPRODUCT(--(A7:A9999-DAY(A7:A9999)+1=F1),M7:M9999)