Shorten a SUMIF formula

[rojashan]

Hi,

I need help in shortening my formula as it will exceed the 8192 characters.

Below is my formula:

=SUMIFS('Data Dump'!$L$4:$L$99987,'Data Dump'!$L$4:$L$99987,">=1",'Data Dump'!$F$4:$F$99987,">="&DAR!$G12,'Data Dump'!$F$4:$F$99987,"<="&DAR!$H12,'Data Dump'!$H$4:$H$99987,$DA12,'Data Dump'!$D$4:$D$99987,">="&DAR!$J12,'Data Dump'!$E$4:$E$99987,"<="&DAR!$K12,'Data Dump'!$B$4:$B$99987,$DF12)


However, I need to add aside from $DF12 to include as well $DG12 and $DH12, afterwards change $DA12 to $DB12, $DC12

My original formula have 9 combinations of above formula which is too long.

Each DA column will add DF, DG, DH
Each DB column will add DF, DG, DH
and so on.

[oeldere]

Maybe you need to change the data (e.g. in a vertically way), so you could use a pivot table.

[rojashan]

I can't as I need to put new set of data vertically

[oeldere]

Probably it can be done with VBA. (maybe not by me).

[JohnTopley]

Perhaps post a sample file so we can see what you are doing as it seems (is!) a very complex summation.

And VBA may be the (only?) answer.

[rojashan]

Maybe it will help if I will describe my spreadsheet.

I have two spreadsheets.

Spreadsheet 1: "Data Dump" is where I paste all my data pulled out from our system. Example: all the sales we have for the financial year 2015-2016

Column L: list the number of nights sold
Culumn F: booking date
Column H: is rate code
Column D: is arrival date
Column E: is departure date
Column B: is room type

Spreadsheet 2: "DAR1516" is where I summarize the data based on the sale as we have different promos
Column G is sale start
Column H is sale end
Column J is travel start
Column K is travel end
Column DA-DE is the different rate code that makes up a sale
Column DF-DL is the different room type that is included in the sale

what i wanted is that in I want to add the no of nights (sheet1, column L) that is

1. booked (sheet1, column f) between the sale period (sheet 2, column G & H)
2. arrival and check-in date is between the travel period (sheet 2, column J & K)
3. that adds up all the rate plan in the sale (sheet 2, DA and/or DB, and/or DC, and/or DD, and/or DE)
4 that adds up all the room type (sheet 2, DF and/or DG,and/or DH, and/or, DI, and/or DJ, and/or DK, and/or DL)

Hope above makes more sense.

thanks

[JohnTopley]

A file would still help: no one wants to type in data to test any formulae.

And reading your description, I am not sure I follow the logic: you appear to have "ad hoc" mixture of dates, rates and room types: I suspect SUMPRODUCT is maybe what you need.

[Pete_UK]

If the long composite formula is going to be too long, why don't you put the shorter formulae in separate columns (which could be hidden), and then sum the results from those columns?

Hope this helps.

Pete