using logical functions as criteria with the SUMIF function
 

I have a datatable with a number of columns and I want to add all the numbers
in column "S" when the text in column "M begin with an "A". I also want to
use the same function for all the numbers in Cloumns "T", "U", "V", etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all I get is
"0". Any suggestions?

try
=sumproduct((left(m3:m100,1)="A")*s3:s100)
=sumproduct((left(m3:m100,1)="A")*s3:v100)


--
Don Guillett
SalesAid Software

"pfdubz" wrote in message
...
I have a datatable with a number of columns and I want to add all the
numbers
in column "S" when the text in column "M begin with an "A". I also want
to
use the same function for all the numbers in Cloumns "T", "U", "V", etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all I get is
"0". Any suggestions?

Hi

=SUMPRODUCT(--(LEFT(M3:M300)="A"),S3:S300)

The formula above sums all rows with entriea im column M starting with
either "a" or "A". A case sensitive variant of same formula is
=SUMPRODUCT(--(EXACT(LEFT(D1:D5),"A")),C1:C5)


Arvi Laanemets


"pfdubz" wrote in message
...
I have a datatable with a number of columns and I want to add all the
numbers
in column "S" when the text in column "M begin with an "A". I also want
to
use the same function for all the numbers in Cloumns "T", "U", "V", etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all I get is
"0". Any suggestions?

I am not trying to multiply column M and column S. I am trying to add all
the numbers in column S that correspond with text in column M that begins
with the letter "A"

"Don Guillett" wrote:

try
=sumproduct((left(m3:m100,1)="A")*s3:s100)
=sumproduct((left(m3:m100,1)="A")*s3:v100)


--
Don Guillett
SalesAid Software

"pfdubz" wrote in message
...
I have a datatable with a number of columns and I want to add all the
numbers
in column "S" when the text in column "M begin with an "A". I also want
to
use the same function for all the numbers in Cloumns "T", "U", "V", etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all I get is
"0". Any suggestions?

Hi
and this is just what this formula does. Just try it. For an
explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"pfdubz" schrieb im Newsbeitrag
...
I am not trying to multiply column M and column S. I am trying to
add all
the numbers in column S that correspond with text in column M that
begins
with the letter "A"

"Don Guillett" wrote:

try
=sumproduct((left(m3:m100,1)="A")*s3:s100)
=sumproduct((left(m3:m100,1)="A")*s3:v100)


--
Don Guillett
SalesAid Software

"pfdubz" wrote in message
...
I have a datatable with a number of columns and I want to add all
the
numbers
in column "S" when the text in column "M begin with an "A". I
also want
to
use the same function for all the numbers in Cloumns "T", "U",
"V", etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all
I get is
"0". Any suggestions?

Thanks for the reference Frank. It is most illuminating and it works!
..
"Frank Kabel" wrote:

Hi
and this is just what this formula does. Just try it. For an
explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"pfdubz" schrieb im Newsbeitrag
...
I am not trying to multiply column M and column S. I am trying to
add all
the numbers in column S that correspond with text in column M that
begins
with the letter "A"

"Don Guillett" wrote:

try
=sumproduct((left(m3:m100,1)="A")*s3:s100)
=sumproduct((left(m3:m100,1)="A")*s3:v100)


--
Don Guillett
SalesAid Software

"pfdubz" wrote in message
...
I have a datatable with a number of columns and I want to add all
the
numbers
in column "S" when the text in column "M begin with an "A". I
also want
to
use the same function for all the numbers in Cloumns "T", "U",
"V", etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all
I get is
"0". Any suggestions?

Best to try a solution before responding as you did.

--
Don Guillett
SalesAid Software

"pfdubz" wrote in message
...
I am not trying to multiply column M and column S. I am trying to add all
the numbers in column S that correspond with text in column M that begins
with the letter "A"

"Don Guillett" wrote:

try
=sumproduct((left(m3:m100,1)="A")*s3:s100)
=sumproduct((left(m3:m100,1)="A")*s3:v100)


--
Don Guillett
SalesAid Software

"pfdubz" wrote in message
...
I have a datatable with a number of columns and I want to add all the
numbers
in column "S" when the text in column "M begin with an "A". I also
want
to
use the same function for all the numbers in Cloumns "T", "U", "V",
etc.
I have tried =SUMIF($M$3:$M$100,LEFT($M3,1)="A",S3:S100) and all I get
is
"0". Any suggestions?