Summing up based on a rather awkward critera

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
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 ?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,132,679
Messages
5,654,713
Members
418,149
Latest member
tjanok

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
Top