SumProduct Question

[Gina]

The numbers I want to total are in Column M

=SUMPRODUCT((Data!D$1$2550>=Lists!A13)*(Data!D$1$2550<Lists!B13)*(Data!J$1:J$2550="Saint Paul")*(Data!M$1:M$2550>0))

What I am trying to say is-

Look at the data in column D of the "Lists" worksheet. When the value of
the data (this happens to be a date) is >= the first day of a given year, and
< the 1st day of the next year, and the city which is recorded in Column J is
"Saint Paul", please total the numbers in column M.

This doesn't seem to be working quite right though.

 

[John C]

Why do you have >0 for the M column? You are asking the formula if column m
is >0, and it will give a response of 1 or 0 (true or false).

=SUMPRODUCT(--(Data!D$1$2550>=Lists!A13),--(Data!D$1$2550<Lists!B13),--(Data!J$1:J$2550="Saint Paul"),(Data!M$1:M$2550))

Hope this helps.

 

[Gina]

I put it that way because I wanted it to count values of 1 and higher.

 

[Gina]

I've just tested out the format you provided, and it works where mine didn't.
Thank you very much. I wasn't sure how to do that last part, and you nailed
it.
Gina

 

[John C]

Well, then try this, and it will sum values only of 1 or higher
=SUMPRODUCT(--(Data!D$1$2550>=Lists!A13),--(Data!D$1$2550<Lists!B13),--(Data!J$1:J$2550="Saint Paul"),--(Data!M$1:M$2550>=1),(Data!M$1:M$2550))

If you leave formula as I left it, it will sum values less than 1, including
any negative numbers.