oh - I posted too soon. Sumproduct is the way to go. I am having another
read through the link.
--
Thanks
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of <"Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500<"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500<"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine