sumproduct problem

[robcarr]

hi,

i am trying to get this formula to work, but it always shows 0 with array entered or not

=SUMPRODUCT((Agent!$C$3:$C$4000=$D$2)*(Agent!$V$3:$V$4000)*(Agent!$AS$3:$AS$4000=$I$2))

range c contains dates, D2 is the date reference, v is the range i need to sum (these are formulas that show a value in currency format), as is the a word i need to check, i2 is the reference for the word.

i can get the formula to work without the as part, but as soon as i add the as part it shows 0.

I cant for the life of me think why it is doing this.

The AS cells are a formula, and either show "Westaff", "EDGE" or 0.

one mopre thing the data that is being summed is subtotaled, this cant be changed.

any help better way of doing is greatly appreciated.

I only wnat to do this via a formula, i know it can be done via pivottable etc, but i dont want to go down this route.

Hope this is of use, Rob.

 

[robcarr]

being really stupid fixed it

Hope this is of use, Rob.