Sumproduct with horizontal and vertical criterion

[Will31]

Hi All,

I have a dataset from A2 to GI3503 which has the forecast month in column B (i.e. Nov-12), the months forecasted in D (i.e. Jan-12 to Dec-16), the header A2 to GI2 which has categories of work.

I have tried the following formula to sum based on the month of forecast, month forecasted and work category but it doesn't work:

Please Login or Register  to view this content.

I get a #Value error

'Staging Sheet'!$B$127 = Month of forecast i.e. Nov-12
'Staging Sheet'!D142 = Start of quarter i.e. Jan-12
'Staging Sheet'!E142 = Start of next quarter i.e. Apr-12
'Staging Sheet'!B143 = a client name that is on the header (Row 2)

Any ideas?

Regards

Will

[dilipandey]

Hi Will 32,

Please upload a sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[benishiryo]

hi will, the problem is probably due to your 4th & 5th brackets. this might solve it.
=SUMPRODUCT(('Rev Dataset'!$B$3:$B$3503='Staging Sheet'!$B$127)*('Rev Dataset'!$D$3:$D$3503>'Staging Sheet'!D142)*('Rev Dataset'!$D$3:$D$3503<'Staging Sheet'!E142)*(OFFSET('Rev Dataset'!$N$3,0,MATCH('Staging Sheet'!B143,'Rev Dataset'!$N$2:$GF$2,0),3501)))

do note that the "3501" is based on your 3503 rows - 2 rows on top which are probably your headers & a blank. adjust accordingly