Thanks for the link. It looks like I am using sumproduct incorrectly. What
I am trying to do is:
- sum all the values in column J if column A is = to Q1, Q2 or Q3, and if
column B is equal to 50.
- there are some instances where I would need to sum all the values in
column J if column A is = to Q1, Q2 or Q3, and if column B is equal to 50,
and if column B is equal to 51.

The values would change of course depending on what cell or sheet I am in.

I've noticed that sometimes the answers are abbreviated. Being a bit of a
novice I dont quite understand the intent so if you could do a long answer to
the problem above, that would be wonderful.

Another thing I dont understand and which appears to be part of the issue
above, is how to tell whether a column is numeric or general and why nothing
changed in my searches when I changed the format from general to numeric.

--
Thank you very much
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