Prev: Vlookup or Hlookup does not work
Next: changin date dd/mm/yy into Month
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: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 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: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: 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: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.
>

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