sumproduct

[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))

 

[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(TRANSPOSE(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]

What version of Excel are you using?

 

[pierre]

im using EXCEL 2003

What version of Excel are you using?

 

[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$22)<=$D$1,INDEX(A$1:A$10,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<>"",$C$1:$C$3)),$A$1:$A$10)),ROW(A$1:A$10)),ROWS(D$22))),"")

 

[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]

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

 

[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!