Summing up based on a rather awkward critera

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,005
I am assuming I need to use SUMPRODUCT on this request but can't work out all the criteria

A snapshot of the data is

Tailormade Reservation Report Template.xls
ABCDEFGH
1TAILORMADERESERVATIONREPORT28FEB10-6
2PART=TOURF10049F1011F1020F103
3PART=PACKF20052F2014F2020F203
4PART=HTLF300101F301510F30226F303
5PART=TRFF4000F401121F40214F403
6PART=BROCF5000F5010F5020F503
7PART=AREAF600107F60132F602171F603
8TAILORMADERESERVATIONREPORT28FEB10-1
9PART=TOURF1005F1010F1020F103
10PART=PACKF2005F2011F2020F203
11PART=HTLF30019F30183F30212F303
12PART=TRFF4000F40119F40210F403
13PART=BROCF5000F5010F5020F503
14PART=AREAF6008F6013F60227F603
15TAILORMADERESERVATIONREPORT01MAR10-6
16PART=TOURF1009F1011F1020F103
17PART=PACKF20041F2012F2020F203
18PART=HTLF30034F301243F30211F303
Input (Download)



This goes beyond column H but I hope you get the idea...

On another sheet, I want to sum up everything in the adjacent column to F516, F517,F526,F519,F545,F520,F530,F532 based on a date being selected

So the user will choose, for example 28 Feb 10 and then we should get added up all the cells adjacent to the codes I mention above in between rows 9 and 14

Can anyone help please, I had started with the following idea

=SUMPRODUCT(--(MOD(COLUMN('Input (Download)'!A:CS),2)

As the codes with F are always in a column which is divisable by 2, then I would sum up using offset, by offsetting one column. I was then going to put all the F codes into a named range and maybe use an array....but I slightly lost the plot as how to piece it together.............any ideas please ?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,495
Messages
5,511,659
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top