Prev: Vlookup or Hlookup does not work
Next: changin date dd/mm/yy into Month
From: Rbirdie on 5 Mar 2010 09:20
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: Rbirdie on 5 Mar 2010 09:29
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: Mike H on 5 Mar 2010 09:36
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: Rbirdie on 5 Mar 2010 09:37
Thanks for the support.

I have updated to
=SUMPRODUCT(--(Detail!A2:A12440 ="C"),--(Detail!M2:M12440
=""),(Detail!L2:L12440))

I am still getting "0".

What is strange is that if I change the formula to
=SUMPRODUCT(--(Detail!A2:A12440 >"C"),--(Detail!M2:M12440
=""),(Detail!L2:L12440))
I receive the value of all regions greater than C with no notes. It works.


"Per Jessen" wrote:

> 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:34061B9C-00CA-4DD9-A6FB-1F04A7F273EC(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: Rbirdie on 5 Mar 2010 09:50
Thanks John- It is still returning 0.


"John" wrote:

> 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:34061B9C-00CA-4DD9-A6FB-1F04A7F273EC(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.
> >
>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Vlookup or Hlookup does not work
Next: changin date dd/mm/yy into Month