SumProduct Question
 

The numbers I want to total are in Column M

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

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.

SumProduct Question
 

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:D$2550=Lists!A13),--(Data!D$1:D$2550<Lists!B13),--(Data!J$1:J$2550="Saint Paul"),(Data!M$1:M$2550))

Hope this helps.
--
John C


"Gina" wrote:

The numbers I want to total are in Column M

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

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.

SumProduct Question
 

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

"John C" wrote:

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:D$2550=Lists!A13),--(Data!D$1:D$2550<Lists!B13),--(Data!J$1:J$2550="Saint Paul"),(Data!M$1:M$2550))

Hope this helps.
--
John C


"Gina" wrote:

The numbers I want to total are in Column M

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

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.

SumProduct Question
 

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" wrote:

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:D$2550=Lists!A13),--(Data!D$1:D$2550<Lists!B13),--(Data!J$1:J$2550="Saint Paul"),(Data!M$1:M$2550))

Hope this helps.
--
John C


"Gina" wrote:

The numbers I want to total are in Column M

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

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.

SumProduct Question
 

Well, then try this, and it will sum values only of 1 or higher:
=SUMPRODUCT(--(Data!D$1:D$2550=Lists!A13),--(Data!D$1:D$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.

--
John C


"Gina" wrote:

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

"John C" wrote:

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:D$2550=Lists!A13),--(Data!D$1:D$2550<Lists!B13),--(Data!J$1:J$2550="Saint Paul"),(Data!M$1:M$2550))

Hope this helps.
--
John C


"Gina" wrote:

The numbers I want to total are in Column M

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

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.