Prev: Vlookup or Hlookup does not work
Next: changin date dd/mm/yy into Month
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
>
> Edit|Paste 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:16416F84-07E9-4009-AB9B-E5EEF5A67226(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:577CDF06-13CE-4641-A377-E51D6F649529(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:7C399820-3ECB-47AA-934B-03BD6C3C12FB(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
>>
>> Edit|Paste 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