Struggling with Sumproduct

[philn]

Hi

Reposting as still and issue.

I have a spreadsheet with accounts transaction data where column a = date,
column b = nominal code, Column c = department name, column d = department
number, column e = amount.

I need to query the columns to sum transactions for a particular month for a
particular nominal code, for a particular department.
The department name is only included to identify the department number, and
will not be used.

My initial thoughts are
=SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D216202=1)*(Sheet1!E2:E16202))

However it returns a zero value.

Once i have achieved this I will need to increase the range to include
currently empty cells, as when the accounts data is refreshed it will
increase the range of occupied cells, which i believe i could proabaly
achieve by including the formula in an If statement.

Any help greatly appreciated.

 

[Don Guillett]

Notice a couple of changes
=SUMPRODUCT((month(Sheet1!A2:A16202)=6)*(Sheet1!B2:B16202="4000")*(Sheet1!D216202=1)*Sheet1!E2:E16202)

 

[Bernie Deitrick]

Phil,

MONTH(Sheet1!A2:A16202)=6

would be the way to specify the month.

And, maybe, change "4000" to 4000

But a better way for any of this would be to use a pivot table.

HTH,
Bernie
MS Excel MVP

 

[philn]

Thanks Don noted the changes to both date and value fileds, now works a treat
so onto the next faze!

 

[philn]

Thanks Bernie, unable to use a pivot as dragging data into an existing report
format