[Pierre]

hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE BEFORE.
HELP

[T. Valko]

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE BEFORE.
HELP

[Pierre]

it worked perfectly well....THANKS.

as for the #num! that you where telling me that there is must be errors
somehwere in column G and/or H......YES YOU ARE RIGHT :
MY FORMULA IN COLUMN H IS GIVING CORRECT ANSWERS EXCEPT FOR 2 LAST CELLS AS
I DRAGGED DOWN :

G H
2 a 30
3 a 20
4 b 40
#num!
#num!


MY FORMULA IN COLUMN H WAS :

{=INDEX(B$1:B$10;SMALL(IF(ISNUMBER(SEARCH(TRANSPOS E(IF(C$1:C$3<"";C$1:C$3));A$1:A$10));ROW(B$1:B$10 )-MIN(ROW(B$1:B$10))+1);ROWS($H$2:H2)))}
and i dragged down

I TRIED TO DO : =IF(ISERROR(FORMULA......
BUT IT DIDNT WORK....HAVE YOU AN IDEA ABOUT HOW TO GET RID OF THE #NUM!
THING ??

THANK YOU



"T. Valko" wrote:

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE BEFORE.
HELP

[T. Valko]

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
it worked perfectly well....THANKS.

as for the #num! that you where telling me that there is must be errors
somehwere in column G and/or H......YES YOU ARE RIGHT :
MY FORMULA IN COLUMN H IS GIVING CORRECT ANSWERS EXCEPT FOR 2 LAST CELLS
AS
I DRAGGED DOWN :

G H
2 a 30
3 a 20
4 b 40
#num!
#num!


MY FORMULA IN COLUMN H WAS :

{=INDEX(B$1:B$10;SMALL(IF(ISNUMBER(SEARCH(TRANSPOS E(IF(C$1:C$3<"";C$1:C$3));A$1:A$10));ROW(B$1:B$10 )-MIN(ROW(B$1:B$10))+1);ROWS($H$2:H2)))}
and i dragged down

I TRIED TO DO : =IF(ISERROR(FORMULA......
BUT IT DIDNT WORK....HAVE YOU AN IDEA ABOUT HOW TO GET RID OF THE #NUM!
THING ??

THANK YOU



"T. Valko" wrote:

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE
BEFORE.
HELP

[Pierre]

im using EXCEL 2003





"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
it worked perfectly well....THANKS.

as for the #num! that you where telling me that there is must be errors
somehwere in column G and/or H......YES YOU ARE RIGHT :
MY FORMULA IN COLUMN H IS GIVING CORRECT ANSWERS EXCEPT FOR 2 LAST CELLS
AS
I DRAGGED DOWN :

G H
2 a 30
3 a 20
4 b 40
#num!
#num!


MY FORMULA IN COLUMN H WAS :

{=INDEX(B$1:B$10;SMALL(IF(ISNUMBER(SEARCH(TRANSPOS E(IF(C$1:C$3<"";C$1:C$3));A$1:A$10));ROW(B$1:B$10 )-MIN(ROW(B$1:B$10))+1);ROWS($H$2:H2)))}
and i dragged down

I TRIED TO DO : =IF(ISERROR(FORMULA......
BUT IT DIDNT WORK....HAVE YOU AN IDEA ABOUT HOW TO GET RID OF THE #NUM!
THING ??

THANK YOU



"T. Valko" wrote:

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE
BEFORE.
HELP

[T. Valko]

Ok...

When you have complex formulas and *expect* that there will be errors
generated then it's probably desireable to prevent those errors.

Since the formulas involved are complex and are calculation intensive it's
in your best interest to prevent the expected errors in the most efficient
manner possible.

You can either build an error trap directly into the formula or you can use
a separate cell that holds the error trap formula. Which of these methods
you choose depends on several factors. Building the error trap directly into
the formula will make the already long complex formula even longer.

Based on your formula I would suggest using a separate cell with a trap
formula. I've shown you how to do this in your previous posts.

Use a separate cell to hold a trap formula and refer to that cell:

Array entered in D1:

=SUMPRODUCT(COUNTIF(A1:A10,"*"&IF(C1:C3<"",C1:C3, "skip")&"*"))

Then (also array entered):

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$10,SMALL(IF(ISN UMBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3) ),$A$1:$A$10)),ROW(A$1:A$10)),ROWS(D$2:D2))),"")


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
im using EXCEL 2003





"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
it worked perfectly well....THANKS.

as for the #num! that you where telling me that there is must be
errors
somehwere in column G and/or H......YES YOU ARE RIGHT :
MY FORMULA IN COLUMN H IS GIVING CORRECT ANSWERS EXCEPT FOR 2 LAST
CELLS
AS
I DRAGGED DOWN :

G H
2 a 30
3 a 20
4 b 40
#num!
#num!


MY FORMULA IN COLUMN H WAS :

{=INDEX(B$1:B$10;SMALL(IF(ISNUMBER(SEARCH(TRANSPOS E(IF(C$1:C$3<"";C$1:C$3));A$1:A$10));ROW(B$1:B$10 )-MIN(ROW(B$1:B$10))+1);ROWS($H$2:H2)))}
and i dragged down

I TRIED TO DO : =IF(ISERROR(FORMULA......
BUT IT DIDNT WORK....HAVE YOU AN IDEA ABOUT HOW TO GET RID OF THE
#NUM!
THING ??

THANK YOU



"T. Valko" wrote:

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE
BEFORE.
HELP

[Pierre]

okay....i got your point very clearly.

but i would like to know something:
the trap formula used in the separated cell D1:
{=SUMPRODUCT(COUNTIF(A1:A10,"*"&IF(C1:C3<"",C1:C3 ,"skip")&"*"))}

IT IS USED TO FIND FOR WORDS....

IF I WOULD LIKE TO FIND VALUES FOR THE CORRESPONDING WORDS...WILL IT BE THE
SAME ??? IF NOT....HOW TO MODIFY THE FORMULA IN D1 ???










"T. Valko" wrote:

Ok...

When you have complex formulas and *expect* that there will be errors
generated then it's probably desireable to prevent those errors.

Since the formulas involved are complex and are calculation intensive it's
in your best interest to prevent the expected errors in the most efficient
manner possible.

You can either build an error trap directly into the formula or you can use
a separate cell that holds the error trap formula. Which of these methods
you choose depends on several factors. Building the error trap directly into
the formula will make the already long complex formula even longer.

Based on your formula I would suggest using a separate cell with a trap
formula. I've shown you how to do this in your previous posts.

Use a separate cell to hold a trap formula and refer to that cell:

Array entered in D1:

=SUMPRODUCT(COUNTIF(A1:A10,"*"&IF(C1:C3<"",C1:C3, "skip")&"*"))

Then (also array entered):

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$10,SMALL(IF(ISN UMBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3) ),$A$1:$A$10)),ROW(A$1:A$10)),ROWS(D$2:D2))),"")


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
im using EXCEL 2003





"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
it worked perfectly well....THANKS.

as for the #num! that you where telling me that there is must be
errors
somehwere in column G and/or H......YES YOU ARE RIGHT :
MY FORMULA IN COLUMN H IS GIVING CORRECT ANSWERS EXCEPT FOR 2 LAST
CELLS
AS
I DRAGGED DOWN :

G H
2 a 30
3 a 20
4 b 40
#num!
#num!


MY FORMULA IN COLUMN H WAS :

{=INDEX(B$1:B$10;SMALL(IF(ISNUMBER(SEARCH(TRANSPOS E(IF(C$1:C$3<"";C$1:C$3));A$1:A$10));ROW(B$1:B$10 )-MIN(ROW(B$1:B$10))+1);ROWS($H$2:H2)))}
and i dragged down

I TRIED TO DO : =IF(ISERROR(FORMULA......
BUT IT DIDNT WORK....HAVE YOU AN IDEA ABOUT HOW TO GET RID OF THE
#NUM!
THING ??

THANK YOU



"T. Valko" wrote:

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY MADE
BEFORE.
HELP

[T. Valko]

It will be the same as long as there are no words with corresponding empty
cells. Like this:

word....10
word....20
word........
weed....30
weed....40

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
okay....i got your point very clearly.

but i would like to know something:
the trap formula used in the separated cell D1:
{=SUMPRODUCT(COUNTIF(A1:A10,"*"&IF(C1:C3<"",C1:C3 ,"skip")&"*"))}

IT IS USED TO FIND FOR WORDS....

IF I WOULD LIKE TO FIND VALUES FOR THE CORRESPONDING WORDS...WILL IT BE
THE
SAME ??? IF NOT....HOW TO MODIFY THE FORMULA IN D1 ???










"T. Valko" wrote:

Ok...

When you have complex formulas and *expect* that there will be errors
generated then it's probably desireable to prevent those errors.

Since the formulas involved are complex and are calculation intensive
it's
in your best interest to prevent the expected errors in the most
efficient
manner possible.

You can either build an error trap directly into the formula or you can
use
a separate cell that holds the error trap formula. Which of these methods
you choose depends on several factors. Building the error trap directly
into
the formula will make the already long complex formula even longer.

Based on your formula I would suggest using a separate cell with a trap
formula. I've shown you how to do this in your previous posts.

Use a separate cell to hold a trap formula and refer to that cell:

Array entered in D1:

=SUMPRODUCT(COUNTIF(A1:A10,"*"&IF(C1:C3<"",C1:C3, "skip")&"*"))

Then (also array entered):

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$10,SMALL(IF(ISN UMBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3) ),$A$1:$A$10)),ROW(A$1:A$10)),ROWS(D$2:D2))),"")


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
im using EXCEL 2003





"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
it worked perfectly well....THANKS.

as for the #num! that you where telling me that there is must be
errors
somehwere in column G and/or H......YES YOU ARE RIGHT :
MY FORMULA IN COLUMN H IS GIVING CORRECT ANSWERS EXCEPT FOR 2 LAST
CELLS
AS
I DRAGGED DOWN :

G H
2 a 30
3 a 20
4 b 40
#num!
#num!


MY FORMULA IN COLUMN H WAS :

{=INDEX(B$1:B$10;SMALL(IF(ISNUMBER(SEARCH(TRANSPOS E(IF(C$1:C$3<"";C$1:C$3));A$1:A$10));ROW(B$1:B$10 )-MIN(ROW(B$1:B$10))+1);ROWS($H$2:H2)))}
and i dragged down

I TRIED TO DO : =IF(ISERROR(FORMULA......
BUT IT DIDNT WORK....HAVE YOU AN IDEA ABOUT HOW TO GET RID OF THE
#NUM!
THING ??

THANK YOU



"T. Valko" wrote:

There's nothing wrong with your formula.

There must be #NUM! errors somehwere in column G and/or H.

Try this:

=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$100,G2),"" ,SUMIF($G$2:$G$100,G2,$H$2:$H$100))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hi
i have :

G H
2 a 30
3 a 20
4 b 40

i inserted in cell J2 :
{=IF(COUNTIF($G$2:G2;G2)<COUNTIF($G$2:$G$100;G2);" ";SUMPRODUCT(--($G$2:$G$100=G2);($H$2:$H$100)))}

here i should get : for a = 50 (WHICH IS 30+20)
for b = 40 (FOR CELL G4)

BUT MY PROBLEM IS THAT I GET INSTEAD OF THESE NUMBERS : #NUM!

NB: COLUMN G AND COLUMN H ARE RESULTS OF FORMULAS PREVIOUSLY
MADE
BEFORE.
HELP

[Pierre]

it worked smoothly
THANK you very much for your consistent help and support and for your
PATIENCE.
THANK YOU VERY MUCH INDEED...............

[T. Valko]

You're very welcome!

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
it worked smoothly
THANK you very much for your consistent help and support and for your
PATIENCE.
THANK YOU VERY MUCH INDEED...............