Ms Excel 2007/2010 Lookup / Match Function

Another Formula in E3:

=IF(SUMPRODUCT(ShiftData!$F$4:$F$10*(ShiftData!$B$4:$B$10=$A3)*(ShiftData!$C$4:$C$10=B$1))=0,0,D3-SUMPRODUCT(ShiftData!$F$4:$F$10*(ShiftData!$B$4:$B$10=$A3)*(ShiftData!$C$4:$C$10=B$1)))

A somewhat shorter formula takes advantage of the fact that dividing by zero causes an error. As a result, you only need to perform the SUMIFS once:
=IFERROR(D3-1/(1/SUMIFS(ShiftData!$F:$F,ShiftData!$C:$C,'CM01-RawData'!B$1,ShiftData!$B:$B,'CM01-RawData'!$A3)),0)

It is worth noting that the parentheses surrounding (1/SUMIFS(....)) are necessary for the formula to work.

Brad

A note about the Sumproduct suggestion:

While it works, it uses fixed ranges. Assuming that the data on the ShiftData sheet will grow, you would need a solution that dynamically updates the required range. This can be done with a dynamic range name.

With the Sumifs suggestion, that extra work is not required. Sumifs can work with whole columns.

Since Excel 2007, Sumproduct can also use whole columns, but since it works like an array formula, that would be performing really slowly.

When it comes to performance, Sumifs beats Sumproduct by lengths, while at the same time no adjustments to the ranges are required.

cheers, teylyn

teylyn - I was not aware SUMIFS can take an entire column and handle it without resource drain, while addressing a whole column is not compatible with Excel 2003 and for Excel 2007+ (extrapolating to Excel 2010, correct?) is compatible but not less efficient as it then functions as a constructed array formula).

If this is so, its quite interesting, as SUMPRODUCT is already an array function, but it transforms itself (Excel 2007+) to accommodate entire columns.

Have I interpreted your comment correctly?

I would add that SUMIFS also graciously ignores column headers, whereas SUMPRODUCT in either form, which includes column headers would ring an error on a numeric data comparison.

For backwards compatibility and multiple criteria, I assume SUMPRODUCT would win out over other alternatives, unless one built multi-criteria keys and used SUMIF (probably most efficient if one has the leeway to do this, and performance due to number of calculations would become an issue in an intensive model).

Dave

Sumproduct can handle text/number comparisons just fine if you choose the suitable syntax. The * operator throws an error, but the double unary doesn't. See here:

http://www.teylyn.com/articles/excel-articles/sumproduct-error-messages/

Opening up Sumproduct to whole columns was not a good idea, as far as I'm concerned. Backwards compatibility is one thing, but performance is really the clincher.

With Sumifs, the backwards compatibility is non-existent, true.

So, if you need backwards compatibility, then define dynamic ranges and use these in good old Sumproduct.

Just my $0.02

cheers, teylyn

Thanks.

No resource drain using entire columns with SUMIFS?

Not really. The speed is relative to the amount of data in the column, as far as I can see, but it's still the fastest alternative.

Thanks for the clarification.  You got me initially excited to use SUMIFS with full columns for all I do, now I'll be judicious, lol.

Dave

Give me a wee while and I'll dig out some speed tests. Numbers rule. I'm on the iPad at the kitchen counter, preparing dinner. Mouths need to be fed.  After that, I'll get back to a real computer and find some stuff to back up the claims I made above.

So, see attached.

The spreadsheet contains a timer macro. Select a cell and click the button. You will see the time in milliseconds that the formula took to calculate.

I've included the measures of three runs of each of the four formulas in column K.

The first one is a Sumproduct on whole columns.
The second one is a Sumproduct on a designated range of over 50,000 cells

The third one is a Sumifs on whole colums
The last one is a Sumifs on a designated range of over 50,000 cells.

Note that the result is always the same in column K.

Now take a moment to compare the time values for the three passes. Each pass will deliver a slightly different result, since the computer is busy with things. Also, your computer setup may deliver faster or slower calculations, depending on your processor, memory, etc.

The point I want to make, though, is that a Sumproduct on whole columns is significantly slower than a Sumproduct on a fixed range of over 50k rows.

Whereas there is hardly any difference between a Sumifs on whole columns or fixed range. Actually, sometimes the fixed range was slower than the whole column Sumifs.

Biggest difference, though is that the Sumproduct on fixed range is about 5 to 7 times faster than the Sumproduct on whole columns.

And the Sumifs, regardless of whole columns or fixed range, is about twice as fast as the Sumproduct on fixed range.

There now.

Numbers have spoken.

(formulas and sample data as found at Excel is fun channel on youtube: http://www.youtube.com/user/ExcelIsFun?feature=watch)

cheers, teylyn
SpeedTest.xlsm

Worked as explained.  Thanks