From: Rbirdie on 5 Mar 2010 08:36 I have been trying to figure this out for 3 days. I have 2 criterias that need to be met and then I want the total of a column. I am trying to get a total of each region with notes and without notes. Col A is region Col M are notes on accts Col L are dollars A M L 1. C Notes in field 3.15 2. W Blank 100.50 3. NE Notes in field 600.00 Here is my formula: =SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440 =""),(Detail!L2:L12440)) It is returning 0. If I play with the formula and change the , to  then it does return a value, but one that is not correct. I also tried filling in the BLANKS of column M with a word and that did not help. Please any help is appreciated.
From: Don Guillett on 5 Mar 2010 08:36 As ALWAYS, post your efforts for comments  Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Rbirdie" <Rbirdie(a)discussions.microsoft.com> wrote in message news:577CDF0613CE4641A377E51D6F649529(a)microsoft.com... >I have been trying to figure this out for 3 days. > I have 2 criterias that need to be met and then I want the total of a > column. > > A > > C >
From: Mike H on 5 Mar 2010 08:50 Hi, Try this =SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M12440="")*(Detail!L2:L12440)) I don't understand what you were trying to do with "^C" in your formula. If this doesn't work please explain  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Rbirdie" wrote: > I have been trying to figure this out for 3 days. > I have 2 criterias that need to be met and then I want the total of a column. > I am trying to get a total of each region with notes and without notes. > > > Col A is region > Col M are notes on accts > Col L are dollars > > A M L > > 1. C Notes in field 3.15 > 2. W Blank 100.50 > 3. NE Notes in field 600.00 > > Here is my formula: > =SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440 > =""),(Detail!L2:L12440)) > > It is returning 0. If I play with the formula and change the , to  then it > does return a value, but one that is not correct. > I also tried filling in the BLANKS of column M with a word and that did not > help. > Please any help is appreciated. >
From: Per Jessen on 5 Mar 2010 08:51 Hi If you use , between the arrays, you need  also before the two first arrays, to convert TRUE/FALSE to 1/0, or you can use * between the arrays: =SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440 =""),Detail!L2:L12440) =SUMPRODUCT((Detail!$A2:$A12440 ="^C")*(Detail!$M2:$M12440 ="")*Detail!L2:L12440) Also I am not sure if you should put ^ in front of 'C' Hopes this helps. .... Per "Rbirdie" <Rbirdie(a)discussions.microsoft.com> skrev i meddelelsen news:34061B9C00CA4DD9A6FB1F04A7F273EC(a)microsoft.com... > I have been trying to figure this out for 3 days. > I have 2 criterias that need to be met and then I want the total of a > column. > I am trying to get a total of each region with notes and without notes. > > > Col A is region > Col M are notes on accts > Col L are dollars > > A M L > > 1. C Notes in field 3.15 > 2. W Blank 100.50 > 3. NE Notes in field 600.00 > > Here is my formula: > =SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440 > =""),(Detail!L2:L12440)) > > It is returning 0. If I play with the formula and change the , to  then > it > does return a value, but one that is not correct. > I also tried filling in the BLANKS of column M with a word and that did > not > help. > Please any help is appreciated. >
From: John on 5 Mar 2010 08:59 Hi Not sure I understand your formula , but try this : =SUMPRODUCT((Detail!$A2:$A12440 ="C"),(Detail!$M2:$M12440 =""),(Detail!L2:L12440)) HTH John "Rbirdie" <Rbirdie(a)discussions.microsoft.com> wrote in message news:34061B9C00CA4DD9A6FB1F04A7F273EC(a)microsoft.com... >I have been trying to figure this out for 3 days. > I have 2 criterias that need to be met and then I want the total of a column. > I am trying to get a total of each region with notes and without notes. > > > Col A is region > Col M are notes on accts > Col L are dollars > > A M L > > 1. C Notes in field 3.15 > 2. W Blank 100.50 > 3. NE Notes in field 600.00 > > Here is my formula: > =SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440 > =""),(Detail!L2:L12440)) > > It is returning 0. If I play with the formula and change the , to  then it > does return a value, but one that is not correct. > I also tried filling in the BLANKS of column M with a word and that did not > help. > Please any help is appreciated. >

Next

Last
Pages: 1 2 3 Prev: Vlookup or Hlookup does not work Next: changin date dd/mm/yy into Month 