=SUMPRODUCT formula failure

**BRISBANEBOB** · 02-24-2009, 09:49 PM

No doubt due to a fundamental lack of understanding my SUMPRODUCT formulae (please see attached workbook) is not working. Can someone be kind enough to point out the error of my ways? I've tried every possible combination (except the right one).

Any help appreciated.

**shg** · 02-24-2009, 09:53 PM

G25 and copy across and down,

=SUMPRODUCT( ($E$9:$E$22=$E25) * ($F$9:$F$22=$F25) * G$9:G$22)

**teylyn** · 02-24-2009, 09:55 PM

Hi,

I'm looking at your spreadsheet. Can you explain what you try to calculate?

Do you want to sum or to count? What do you want to sum/count, depending on what condition??

**BRISBANEBOB** · 02-24-2009, 10:20 PM

I'm trying to summarise the array into the four possible conditions (A1, A2, B1, B2)

**BRISBANEBOB** · 02-24-2009, 10:23 PM

=SUMPRODUCT( ($E$9:$E$22=$E25) * ($F$9:$F$22=$F25) * G$9:G$22) works perfectly but what's with the spaces? How does that work?

**NBVC** · 02-24-2009, 10:27 PM

It works with the spaces or without... it's just a matter of aesthetics or preference... but spaces take up character spaces.. so when formulating long formulas, not inserting them is the better thing.

**BRISBANEBOB** · 02-24-2009, 10:29 PM

Thanks for the help

**shg** · 02-24-2009, 11:23 PM

If your formula approaches the length limit due to spaces, you don't have a space problem, you have a design problem.

White space in formulas (other than within quoted strings) is insignificant to evaluation, but adds to readability and therefore maintenance. No one questions the need for white space in code. I wonder why white space in formulas is considered differently.