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
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 ?
A snapshot of the data is
Tailormade Reservation Report Template.xls | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | TAILORMADERESERVATIONREPORT | 28FEB10-6 | ||||||||
2 | PART=TOUR | F100 | 49 | F101 | 1 | F102 | 0 | F103 | ||
3 | PART=PACK | F200 | 52 | F201 | 4 | F202 | 0 | F203 | ||
4 | PART=HTL | F300 | 101 | F301 | 510 | F302 | 26 | F303 | ||
5 | PART=TRF | F400 | 0 | F401 | 121 | F402 | 14 | F403 | ||
6 | PART=BROC | F500 | 0 | F501 | 0 | F502 | 0 | F503 | ||
7 | PART=AREA | F600 | 107 | F601 | 32 | F602 | 171 | F603 | ||
8 | TAILORMADERESERVATIONREPORT | 28FEB10-1 | ||||||||
9 | PART=TOUR | F100 | 5 | F101 | 0 | F102 | 0 | F103 | ||
10 | PART=PACK | F200 | 5 | F201 | 1 | F202 | 0 | F203 | ||
11 | PART=HTL | F300 | 19 | F301 | 83 | F302 | 12 | F303 | ||
12 | PART=TRF | F400 | 0 | F401 | 19 | F402 | 10 | F403 | ||
13 | PART=BROC | F500 | 0 | F501 | 0 | F502 | 0 | F503 | ||
14 | PART=AREA | F600 | 8 | F601 | 3 | F602 | 27 | F603 | ||
15 | TAILORMADERESERVATIONREPORT | 01MAR10-6 | ||||||||
16 | PART=TOUR | F100 | 9 | F101 | 1 | F102 | 0 | F103 | ||
17 | PART=PACK | F200 | 41 | F201 | 2 | F202 | 0 | F203 | ||
18 | PART=HTL | F300 | 34 | F301 | 243 | F302 | 11 | F303 | ||
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 ?