Struggling with sum product/month function

R

Rashie

Board Regular
Joined
Jun 5, 2015
Messages
55
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.
 
Sort by date Sort by votes
You're better off posting a sample spreadsheet with example data and your expected results.

Attach a sample spreadsheet, remove any sensitive data.

You cant attach files on this forum.
Upload the file to an online storage site then place a link to it on this forum.
 
Upvote 0
Are your dates true dates? That is does excel see them as dates or text?
 
Upvote 0
Upvote 0
Aladin Akyurek said:
=SUMPRODUCT(RangeToSum,--(DateRange-DAY(DateRange)+1)=B$1)

where B1 houses 1-Jan-2016.
Click to expand...

Did you try this at all?

Rashie said:
Hey,

https://docs.google.com/spreadsheets/d/11gQEm7QGK-kZ7vtFUiUBrle7x7ISGl-xzAQ2mnwfurc/edit?usp=sharing

Uploaded to google Sheets, let me know if you can view it okay. I warn you though, as it stands it's as ugly as all hell. Once I've worked out my formulas I'm going to make it much smaller and tidy it up.
The cells from H2:S4 are what I would like to auto populate based on the data from row 7.

Any help appreciated :)

Rashie.
Click to expand...

Rashie said:
Yes it does, i've uploaded more of an example. Thanks for he reply
Click to expand...

Instead of an image or a link to outside, use a method, for example one itemized in Attachments or the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl for posting a useable, scaled-down sample.
 
Upvote 0
Thanks Aladin, I couldn't get your code to work I'm afraid.
I need to leave the office for an hour but I'll have another go at trying to resolve this later.

Thanks all for your help.
 
Upvote 0
Hi Again,

Hopefully this is now in a format you can read.
I'm hoping to have the boxes in rows 2, 3 and 4 to be filled out automatically. This is where I'm struggling because i want it to be able to pull out just the relevant entries on the relevant lines based on what date the passengers are departing. So for example in E2 "Pax by Month" I want F2 to just contain Pax that are going out when the departure date is in febuarary. As you can see from my example data there is 2 pax under booking date 01/02/2016 (UK format.)

Any help ? I'm not even sure it's possible.

Thanks,


Code: 
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">



****** http-equiv=Content-Type content="text/html; charset=windows-1252">
****** name=ProgId content=Excel.Sheet>
****** name=Generator content="Microsoft Excel 14">
<link id="Main-File" rel="Main-File
 href=" ..="" low%20cost%20payments%202016%20-%20richard.htm"="">
<link rel="File-List" href="filelist.xml">
<link rel="Stylesheet" href="stylesheet.css">
<style>
  <!--table
      {mso-displayed-decimal-separator:"\.";
      mso-displayed-thousand-separator:"\,";}
  @page
      {margin:.75in .7in .75in .7in;
      mso-header-margin:.3in;
      mso-footer-margin:.3in;}
  -->
  </style>
******** language="JavaScript">

*********>

 
 
[TABLE="width: 1173"]
<tbody>[TR]
[TD="class: xl128, width: 92"][/TD]
[TD="class: xl126, width: 184, colspan: 2"]For board report YTD[/TD]
[TD="class: xl128, width: 92"][/TD]
[TD="class: xl130, width: 92"][/TD]
[TD="class: xl135, width: 92"]Jan[/TD]
[TD="class: xl135, width: 92"]Feb[/TD]
[TD="class: xl135, width: 92"]Mar[/TD]
[TD="class: xl135, width: 92"]Apr[/TD]
[TD="class: xl135, width: 92"]May[/TD]
[TD="class: xl135, width: 92"]Jun[/TD]
[TD="class: xl135, width: 92"]Jul[/TD]
[TD="class: xl135, width: 92"]Aug[/TD]
[TD="class: xl135, width: 92"]Sep[/TD]
[TD="class: xl135, width: 92"]Oct[/TD]
[TD="class: xl135, width: 92"]Nov[/TD]
[TD="class: xl135, width: 92"]Dec[/TD]
[/TR]
[TR]
[TD="class: xl128"][/TD]
[TD="class: xl127, width: 92"]Total Passengers
  Booked[/TD]
[TD="class: xl124"]6[/TD]
[TD="class: xl128"][/TD]
[TD="class: xl136, width: 92"]Pax by Month[/TD]
[TD="class: xl132"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[/TR]
[TR]
[TD="class: xl128"][/TD]
[TD="class: xl127, width: 92"]Cumulative Total
  Spent[/TD]
[TD="class: xl125"]£600.00[/TD]
[TD="class: xl128"][/TD]
[TD="class: xl136, width: 92"]Spend by Month[/TD]
[TD="class: xl132"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[/TR]
[TR]
[TD="class: xl128"][/TD]
[TD="class: xl127, width: 92"]Cumulative
  Profit/Loss[/TD]
[TD="class: xl125"]£394.00[/TD]
[TD="class: xl128"][/TD]
[TD="class: xl136, width: 92"]Profit/Loss by
  Month[/TD]
[TD="class: xl132"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl124"][/TD]
[TD="class: xl133, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[TD="class: xl134, width: 92"][/TD]
[/TR]
[TR]
[TD="class: xl128"][/TD]
[TD="class: xl129, width: 92"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl130, width: 92"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl130, width: 92"][/TD]
[TD="class: xl130, width: 92"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl128"][/TD]
[TD="class: xl130, width: 92"][/TD]
[TD="class: xl131, width: 92"][/TD]
[TD="class: xl131, width: 92"][/TD]
[TD="class: xl131, width: 92"][/TD]
[TD="class: xl131, width: 92"][/TD]
[/TR]
[TR="class: xl116"]
[TD="class: xl118, width: 92"]Booking
  Date[/TD]
[TD="class: xl118, width: 92"]Brand[/TD]
[TD="class: xl118, width: 92"]Tour Code[/TD]
[TD="class: xl118, width: 92"]Departure Date[/TD]
[TD="class: xl118, width: 92"]Lead Surname[/TD]
[TD="class: xl118, width: 92"]Booking Number[/TD]
[TD="class: xl118, width: 92"]Airline[/TD]
[TD="class: xl118, width: 92"]Departure
  Airport[/TD]
[TD="class: xl118, width: 92"]Destination
  Airport[/TD]
[TD="class: xl118, width: 92"]PNR[/TD]
[TD="class: xl118, width: 92"]Agent[/TD]
[TD="class: xl118, width: 92"]Card Used[/TD]
[TD="class: xl119, width: 92"]Pax[/TD]
[TD="class: xl121, width: 92"]Total Spent[/TD]
[TD="class: xl122, width: 92"]Costed PP[/TD]
[TD="class: xl122, width: 92"]Supplement PP[/TD]
[TD="class: xl123, width: 92"]Profit/Loss[/TD]
[/TR]
[TR]
[TD="class: xl117, align: right"]01/01/2016[/TD]
[TD="class: xl115, width: 92"][/TD]
[TD]A1[/TD]
[TD="class: xl117, align: right"]01/03/2016[/TD]
[TD="class: xl114, width: 92"]LAST1[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl113"]AA[/TD]
[TD="class: xl115, width: 92"]DEP[/TD]
[TD="class: xl115, width: 92"]ARR[/TD]
[TD="align: right"]12345[/TD]
[TD="class: xl113"]ME[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl114, width: 92, align: right"]1[/TD]
[TD="class: xl120, width: 92, align: right"]£100.00[/TD]
[TD="class: xl120, width: 92, align: right"]£50.00[/TD]
[TD="class: xl120, width: 92, align: right"]£49.00[/TD]
[TD="class: xl120, width: 92, align: right"]-£1.00[/TD]
[/TR]
[TR]
[TD="class: xl117, align: right"]01/02/2016[/TD]
[TD="class: xl115, width: 92"][/TD]
[TD]A1[/TD]
[TD="class: xl117, align: right"]01/01/2016[/TD]
[TD="class: xl114, width: 92"]LAST2[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl113"]BA[/TD]
[TD="class: xl115, width: 92"]DEP[/TD]
[TD="class: xl115, width: 92"]ARR[/TD]
[TD="align: right"]12345[/TD]
[TD="class: xl113"]ME[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl114, width: 92, align: right"]2[/TD]
[TD="class: xl120, width: 92, align: right"]£200.00[/TD]
[TD="class: xl120, width: 92, align: right"]£100.00[/TD]
[TD="class: xl120, width: 92, align: right"]£49.00[/TD]
[TD="class: xl120, width: 92, align: right"]£98.00[/TD]
[/TR]
[TR]
[TD="class: xl117, align: right"]01/03/2016[/TD]
[TD="class: xl115, width: 92"][/TD]
[TD]A1[/TD]
[TD="class: xl117, align: right"]01/02/2016[/TD]
[TD="class: xl114, width: 92"]LAST3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl113"]AZ[/TD]
[TD="class: xl115, width: 92"]DEP[/TD]
[TD="class: xl115, width: 92"]ARR[/TD]
[TD="align: right"]12345[/TD]
[TD="class: xl113"]ME[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl114, width: 92, align: right"]3[/TD]
[TD="class: xl120, width: 92, align: right"]£300.00[/TD]
[TD="class: xl120, width: 92, align: right"]£150.00[/TD]
[TD="class: xl120, width: 92, align: right"]£49.00[/TD]
[TD="class: xl120, width: 92, align: right"]£297.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="class: xl117"][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl115, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 2"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="colspan: 3"][/TD]
[TD="class: xl114, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92"][/TD]
[TD="class: xl120, width: 92, align: right"]£0.00[/TD]
[/TR]
[TR]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[TD="width: 92"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Summing all numbers that are between start and end date?
Replies
2
Views
415
NickFru
N
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
518
bobaol
B
T
  • Question Question
VBA code to export specific variably named tabs to new workbook?
Replies
2
Views
681
thebigejr
T
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
330
Fluff
Fluff
M
  • Question Question
Summing end of month account balances based on percentages that vary line by line
Replies
3
Views
230
SanjayGMusafir
SanjayGMusafir

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top