|
sumproduct
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 |
sumproduct
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 |
sumproduct
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 |
sumproduct
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 |
sumproduct
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 |
sumproduct
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 |
sumproduct
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 |
sumproduct
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 |
sumproduct
it worked smoothly
THANK you very much for your consistent help and support and for your PATIENCE. THANK YOU VERY MUCH INDEED............... |
sumproduct
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............... |
| All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com