Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct Question | Excel Worksheet Functions | |||
A SUMPRODUCT question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
SUMPRODUCT Question | Excel Discussion (Misc queries) |