SUMPRODUCT Help

[Sam via OfficeKB.com]

Hi All,

I'm using SUMPRODUCT: I've had a look at the site Xldynamic.com but still
having problems.

I'm trying to sum the count of the numeric value housed in cell D4 for the
month of July (for all years).

Results is a Dynamic Range spanning 8 Columns and many Rows. The numeric
value is checked from the 3rd to the 7th Column (spans 5 columns). The date
is a single column (formatted 05/07/2005) - located in the 2nd Column of the
Dynamic Range.

The value housed in cell D4 is a numeric value. The first formula produces
the correct answer but the second version returns an error. I'm not sure
about the syntax and if my parenthesis are in the right position.

The first Formula below gives me the answer I expect:
1) =SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*((MONTH(OFFSET(Results,0,1,,1))
=7)))

The second Formula below produces a #Value error - a value used in the
formula is of the wrong data type?
2)=SUMPRODUCT(--((OFFSET(Results,0,2,,5)=$D$4)),--(MONTH(OFFSET(Results,0,1,,
1))=7))

Can you explain what I've done incorrectly in the second Formula, please.

Thanks
Sam

 

[Domenic]

When using the 'comma syntax', as in your second formula, the range size
for each argument needs to be the same. The first argument gives you a
'5-Column by n-Row' array, whereas the second argument gives you a
'1-Column by n-Row' array, hence the #VALUE! error. (Note that 'n'
represents the number of rows within your dynamic range, which would be
the same for both arguments.)

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much - explanation is clear.

Cheers,
Sam

When using the 'comma syntax', as in your second formula, the range size
for each argument needs to be the same. The first argument gives you a
'5-Column by n-Row' array, whereas the second argument gives you a
'1-Column by n-Row' array, hence the #VALUE! error. (Note that 'n'
represents the number of rows within your dynamic range, which would be
the same for both arguments.)

When using the 'comma syntax', as in your second formula, the range size
for each argument needs to be the same. The first argument gives you a
'5-Column by n-Row' array, whereas the second argument gives you a
'1-Column by n-Row' array, hence the #VALUE! error. (Note that 'n'
represents the number of rows within your dynamic range, which would be
the same for both arguments.)

Hope this helps!

Hi All,

[quoted text clipped - 26 lines]

Thanks
Sam