How can I change the criteria in an array to replace sumproduct?

**dannycarr2k** · 01-06-2012, 06:58 PM

Hi there!

My Excel skills have reached their limit. I have a large, bloated file that's only going to get bigger, so I'm streamlining it - or trying to.

This is the second sheet I'm streamlining - I've attached a file with sheet 1 ("DATA!") and sheet 2 (YEARLY SUMMARIES!".

DATA! has a few header rows and the from row five onwards I write in the data for my flights.
YEARLY SUMMARIES! is where I have the sheet make a summary of my annual flight hours.

On the DATA! sheet, I've used five array formulae and these seemed to have worked great.

On the YEARLY SUMMARIES! sheet, I'm working on columns E, H, K and N - they're all a variation on the same formula. Can I replace the SUMPRODUCT formula with an array? I can't alter the format as this mimics my paper logbook and the entire idea of the sheet is to automate the addition. I've manipulated the formula so it references the date in Column D, shown in MMMM format (i.e. January, February, etc....).

Is there anything I can do to reduce the number of formulae used? Arrays? The problem I have is getting the criterion in arrays to change with each line, and also I have the six month subtotal breaking up the months, which probably stops me using arrays as effectively.

Hope someone likes a challenge and can help!

Excel Forum.xlsx

**DonkeyOte** · 01-06-2012, 07:10 PM

You don't need any arrays on your Data sheet

B5:
=MONTH(A5)
copy down to B10

C5:
=YEAR(C5)
copy down to C10

H5:
=IF(COUNT(E5,G5)<2,"",MOD(G5-E5,1))
format as Time
copied down

I5:
=PRODUCT(D5,F5)
copied down

R5:
=IF(H5="","",HOUR(H5))
copied down

S5:
=IF(H5="","",MINUTE(H5))
copied down

Re: Summaries sheet...

Given your version (XL2011) you can replace SUMPRODUCT with SUMIFS (not available prior to XL2007)

=SUMPRODUCT(--(DATA!$L$5:$L$10="√"),--(DATA!$N$5:$N$10=""),--(DATA!$O$5:$O$10=""),--(DATA!$A$5:$A$10<DATE(YEAR($D4),MONTH($D4)+1,1)),--(DATA!$A$5:$A$10>='YEARLY SUMMARIES'!$D4),DATA!$H$5:$H$10)

becomes

=SUMIFS(DATA!$H$5:$H$10,DATA!$L$5:$L$10,"√",DATA!$N$5:$N$10,"",DATA!$O$5:$O$10,"",DATA!$A$5:$A$10,"<"&DATE(YEAR($D4),MONTH($D4)+1,1),DATA!$A$5:$A$10,">="&'YEARLY SUMMARIES'!$D4)

and so on for remainder of your calculations.

Final point: Arrays are just as inefficient as SUMPRODUCT

**dannycarr2k** · 01-06-2012, 08:21 PM

Thanks DonkeyOte - much appreciated. If I'm looking at expanding the rows of data to, let's say, 10,000 rows; would arrays help reduce file size and calculations then in the main data sheet?

Thanks for the tidied up formulae. I appreciate the summary page isn't too intensive. I have about 4000 rows of data at present and would like to have the sheet handle the next two to three years of flights.

Thanks again! Really appreciated!
DC

**DonkeyOte** · 01-07-2012, 02:51 AM

In terms your file - you're not really using "Arrays" as most would people would interpret an Array.

"Standard" Array formulae like SUMPRODUCT are "expensive" in terms of calculation overhead given their iterative nature along with their use of literal precedent ranges (rather than just used range intersect)

In terms of calculation time etc the SUMIFS suggestion above re: Summaries should perform relatively well. Your Data sheet calculations are all very lightweight in terms of overhead.

The number of calcs. on a sheet isn't really the determining factor in terms of performance but the formulae themselves, for ex.

1000 SUMPRODUCTs on Summaries will be much slower to calculate than 20000 basic formulae on Data sheet.