sumproduct

P

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

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

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

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(ISNUMBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<>"",$C$1:$C$3)),$A$1:$A$10)),ROW(A$1:A$10)),ROWS(D$2:D2))),"")
 
P

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

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
 
P

pierre

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top