SUMPRODUCT Help
 

Hi All,

I'm using the Formula below to sum the count of the numeric value housed in
cell D4 for the month of July.

How can I amend the Formula below to use the MONTH function with a Cell
Reference housing a Custom Date Format: so where I've got = 7 for July, I
would like to use cell B6 which displays 01/07/2005 but is formatted as Jul-
2005 Custom Date mmm-yyyy.

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 01/07/2005) - located in the 2nd Column of the
Dynamic Range.

The value housed in cell D4 is a numeric value.

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*((MONTH( OFFSET(Results,0,1,,1))=7))
)


Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Try...

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*(MONTH(O FFSET(Results,0,1,,1))=
MONTH($B$6)))

Note that if B6 contains a date whose month is January, all empty cells
in your date range will be evaluated as TRUE. You may want to try the
following instead...

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*(MONTH(O FFSET(Results,0,1,,1))=
MONTH($B$6))*(OFFSET(Results,0,1,,1)<""))

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

I'm using the Formula below to sum the count of the numeric value housed in
cell D4 for the month of July.

How can I amend the Formula below to use the MONTH function with a Cell
Reference housing a Custom Date Format: so where I've got = 7 for July, I
would like to use cell B6 which displays 01/07/2005 but is formatted as Jul-
2005 Custom Date mmm-yyyy.

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 01/07/2005) - located in the 2nd Column of the
Dynamic Range.

The value housed in cell D4 is a numeric value.

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*((MONTH( OFFSET(Results,0,1,,1))=7))
)


Thanks
Sam

Hi Domenic,

I used your second option: Formula works great! Thank you very much.

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*(MONTH( OFFSET(Results,0,1,,1))=MONTH($B$6))*(OFFSET(Resul ts,0,1,,1)<""))

Cheers,
Sam

Domenic wrote:
Try...

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*(MONTH( OFFSET(Results,0,1,,1))=
MONTH($B$6)))

Note that if B6 contains a date whose month is January, all empty cells
in your date range will be evaluated as TRUE. You may want to try the
following instead...

=SUMPRODUCT((OFFSET(Results,0,2,,5)=$D$4)*(MONTH( OFFSET(Results,0,1,,1))=
MONTH($B$6))*(OFFSET(Results,0,1,,1)<""))

Hope this helps!

Hi All,

[quoted text clipped - 18 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1