sumif
 

please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on€¦up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot "."
like the ( battery ) and ( mobile )
many thanks

sumif
 

Try this array formula** :

=SUM(IF(ISERR(FIND(".",A1:A10)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on.up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot "."
like the ( battery ) and ( mobile )
many thanks

sumif
 

=sumif(Itm,"*battery*",Amt)

=sumif(Itm,"*mobile*",Amt)

If this post helps click Yes
---------------
Jacob Skaria


"excelFan" wrote:

please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on€¦up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot "."
like the ( battery ) and ( mobile )
many thanks

sumif
 

perhap this, if you want to sum only battery or mobile

=sumif(A1:A10,"battery",B1:B10)
=sumif(A1:A10,"mobile",B1:B10)

if you want to sum BOTH battery and mobile
=SUMIF(A1:A10,"battery",B1:B10)+SUMIF(A1:A10,"mobi le",B1:B10)
--

Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"excelFan" wrote:

please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on€¦up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot "."
like the ( battery ) and ( mobile )
many thanks

sumif
 

Oops. I have misunderstood your query. Biff has answered this.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

=sumif(Itm,"*battery*",Amt)

=sumif(Itm,"*mobile*",Amt)

If this post helps click Yes
---------------
Jacob Skaria


"excelFan" wrote:

please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on€¦up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot "."
like the ( battery ) and ( mobile )
many thanks

sumif
 

Thanks Valko
this array formula works fine if it stand alone, but in my case this
summation segment is only a part of a larger formula. So please is there a
way to avoid writing an array formula
Many Thanks again

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISERR(FIND(".",A1:A10)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on.up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot "."
like the ( battery ) and ( mobile )
many thanks

sumif
 

Normally entered version:

=SUMPRODUCT(--(ISERR(FIND(".",A1:A10))),B1:B10)

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
Thanks Valko
this array formula works fine if it stand alone, but in my case this
summation segment is only a part of a larger formula. So please is there a
way to avoid writing an array formula
Many Thanks again

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISERR(FIND(".",A1:A10)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on.up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot
"."
like the ( battery ) and ( mobile )
many thanks

sumif
 

Many many Thanks T. Valko
This works just perfect
Many thanks again for your help


"T. Valko" wrote:

Normally entered version:

=SUMPRODUCT(--(ISERR(FIND(".",A1:A10))),B1:B10)

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
Thanks Valko
this array formula works fine if it stand alone, but in my case this
summation segment is only a part of a larger formula. So please is there a
way to avoid writing an array formula
Many Thanks again

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISERR(FIND(".",A1:A10)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on.up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot
"."
like the ( battery ) and ( mobile )
many thanks

sumif
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
Many many Thanks T. Valko
This works just perfect
Many thanks again for your help


"T. Valko" wrote:

Normally entered version:

=SUMPRODUCT(--(ISERR(FIND(".",A1:A10))),B1:B10)

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
Thanks Valko
this array formula works fine if it stand alone, but in my case this
summation segment is only a part of a larger formula. So please is
there a
way to avoid writing an array formula
Many Thanks again

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISERR(FIND(".",A1:A10)),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"excelFan" wrote in message
...
please help me

in column A the range Itm contains ticker like
A1 car.3
A2 mobile
A3 battery
A4 mobile.3
A5 mobile
A6 battery
A7 car
A8 mobile.1
A9 car
A10 mobile
and so on.up to over thousand item

in column B the range Amt contains numbers like
B1 -3,589
B2 2,587
B3 -489
B4 2,587
B5 -5,687
B6 1,178
B7 567
B8 -9,642
B9 44,330
B10 13,897

No problem to me to sum those items with the dot "."
for example =sumif(Itm,"*.3",Amt)

I need help please to sum amounts of those which do not have the dot
"."
like the ( battery ) and ( mobile )
many thanks