S
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
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