Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
Hello -
I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
Hi,
Did you try removing the quotes from around TRUE? =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= TRUE)) Mike "MattyP" wrote: Hello - I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
I'm guessing that col_AB contains boolean values.
Try this: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= TRUE)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "MattyP" wrote in message ... Hello - I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
Hi
try this =SUMPRODUCT((Data!K2:K10="AL")*(Data!AB2:AB10=TRUE )) remove "" from TRUE -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked I am an ordinary user trying to assist another Thank You cheers, francis "MattyP" wrote: Hello - I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
Thank you both......that did it ! (For some reason, I think assumed the
"comparison quotes" were needed since the value wasn't an actual #.......I didn't even think to take boolean logic/values into account :( !). Thanks again to you both for your help (and quick replies) --- I appreciate it ! - Matt "Ron Coderre" wrote: I'm guessing that col_AB contains boolean values. Try this: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= TRUE)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "MattyP" wrote in message ... Hello - I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
Hi,
Actually you can simplify your formula in the current case to read: =SUMPRODUCT((Data!K1:K3000="AL")*Data!AB1:AB3000) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "MattyP" wrote: Hello - I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT Function
As long as column AB contains only the logical values TRUE or FALSE (or
empty cells): =SUMPRODUCT(--(Data!K1:K3000="AL"),--Data!AB1:AB3000) If you're using Excel 2007: =COUNTIFS(Data!K1:K3000,"AL",Data!AB1:AB3000,TRUE) -- Biff Microsoft Excel MVP "MattyP" wrote in message ... Hello - I'm in need of some help. I need to count the number of occurences (rows) in a Worksheet, based on two criteria (column entries) in that row. I've been trying to use the 'SUMPRODUCT' function, but keep winding up with a value of 0 (although I know the answer should be 0, when I count portions manually). The formula I've been trying to use is below: =SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE")) Where "Data!" is the tab the information is on (the report/detail I'm attempting to compile in on another tab), column K identifies the state (in this case AL), and column AB is a flag for whether or not a product has been included (the only values are True or False). I've tried everything I can think of, but still can't seem to get this to work. Any help would be most appreciated ! Thanks for your help, - Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct function / VB user defined function | Excel Discussion (Misc queries) | |||
SUMPRODUCT or another function??? | Excel Worksheet Functions | |||
SumProduct function | Excel Worksheet Functions | |||
Sumproduct function | Excel Discussion (Misc queries) | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions |