From: Rbirdie on 5 Mar 2010 10:06 I am getting a "0" for the result. My numbers are numbers, I tested them. I am thinking that the Region (column A) is the issue. Is there a way to do a wildcard with this value? "Mike H" wrote: > Hi, > > Tell us what 'not working' means. Are you getting a #VALUE! error? > > If you are then I suspect that some of your numbers aren't numbers and are > really text. > > Try this formula in an empty column and drag down > =ISNUMBER(L2) > > If your numbers in column L are really numbers then it wiil return TRUE. If > it returns FALSE then they aren't numbers. > > Put a 1 in a cell and copy it. Select column L and then > > EditPaste special  select 'Multiply' OK and try the formula again >  > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Rbirdie" wrote: > > > Thanks for your reply Mike. The ^is before the letters in the Details page. I > > tried taking it out and it does not work either. > > > > Below is returning a "0" > > =SUMPRODUCT((Detail!A2:A12440 ="C")*(Detail!M2:M12440 ="")*(Detail!L2:L12440)) > > > > If I take in less rows, (tried using just 6 as test), it worked. When I do > > all of the data is failing. > > > > "Mike H" wrote: > > > > > 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: Don Guillett on 5 Mar 2010 10:13 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results.  Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Rbirdie" <Rbirdie(a)discussions.microsoft.com> wrote in message news:16416F8407E94009AB9BE5EEF5A67226(a)microsoft.com... > Sorry Don Hit enter in error. Resubmitted with details. > > "Don Guillett" wrote: > >> 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: Bob Phillips on 5 Mar 2010 11:58 You could try this array formula =SUM((RIGHT(Detail!$A2:$A12440,MAX(1,LEN(Detail!$A2:$A12440)1))="C")*(Detail!$M2:$M12440="")*(Detail!L2:L12440))  HTH Bob "Rbirdie" <Rbirdie(a)discussions.microsoft.com> wrote in message news:7C3998203ECB47AA934B03BD6C3C12FB(a)microsoft.com... >I am getting a "0" for the result. > My numbers are numbers, I tested them. I am thinking that the Region > (column > A) is the issue. Is there a way to do a wildcard with this value? > > > > "Mike H" wrote: > >> Hi, >> >> Tell us what 'not working' means. Are you getting a #VALUE! error? >> >> If you are then I suspect that some of your numbers aren't numbers and >> are >> really text. >> >> Try this formula in an empty column and drag down >> =ISNUMBER(L2) >> >> If your numbers in column L are really numbers then it wiil return TRUE. >> If >> it returns FALSE then they aren't numbers. >> >> Put a 1 in a cell and copy it. Select column L and then >> >> EditPaste special  select 'Multiply' OK and try the formula again >>  >> Mike >> >> When competing hypotheses are otherwise equal, adopt the hypothesis that >> introduces the fewest assumptions while still sufficiently answering the >> question. >> >> >> "Rbirdie" wrote: >> >> > Thanks for your reply Mike. The ^is before the letters in the Details >> > page. I >> > tried taking it out and it does not work either. >> > >> > Below is returning a "0" >> > =SUMPRODUCT((Detail!A2:A12440 ="C")*(Detail!M2:M12440 >> > ="")*(Detail!L2:L12440)) >> > >> > If I take in less rows, (tried using just 6 as test), it worked. When I >> > do >> > all of the data is failing. >> > >> > "Mike H" wrote: >> > >> > > 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: pmartglass on 5 Mar 2010 13:29 is it possible that your column M is not actually blank maybe you should try to trim the cell then check for "" '=SUMPRODUCT((Detail!$A2:$A12440 ="C"),(trim(Detail!$M2:$M12440) =""),(Detail!L2:L12440)) "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: Rbirdie on 5 Mar 2010 14:05 Column M is actually blank. But, you bring up a good point. I believe that Column A in some cases has a space after the data. If I can use the trim on column A then it might solve the issue. =SUMPRODUCT((TRIM(Detail!$A3:$A12441) ="C"),(Detail!$M3:$M12441<>""),(Detail!L3:L12441)) THIS WORKS!!!!!!!!!!!!!!!!!! It eliminates that extra space when it is there. > '=SUMPRODUCT((trim(Detail!$A2:$A12440 ="C"),(Detail!$M2:$M12440=""),(Detail!L2:L12440)) "pmartglass" wrote: > is it possible that your column M is not actually blank > maybe you should try to trim the cell then check for "" > > '=SUMPRODUCT((Detail!$A2:$A12440 ="C"),(trim(Detail!$M2:$M12440) > =""),(Detail!L2:L12440)) > > > > "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. > >
First

Prev

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