If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
i HAVE A WORKSHEET WITH 8 COLUMNS
COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#2
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
Hi,
First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#3
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
First of my apologies I did not mean to offend. I did not know that.
I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#4
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
Hi
You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#5
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
Thank you for your advice.
I am using Excel 2000 and thought I could use sumproduct , text after looking at quite a few questions on the community. I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. Thank you again for advice. Roger Govier" wrote: Hi You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#6
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
I take it that what you are saying is that in excel 2000 I cannot get the
cost of all the pvc & pipe I buy in a particular month from the entries I enter. You can. Roger was describing one way to go about it. Here's a formula that will do what you want: Dates = column A Items = column B Amounts = column G =SUMPRODUCT(--(TEXT(A2:A1000,"mmmyyyy")="Jun2007"), --(B2:B1000="PVC & Pipe"),G2:G1000) That will sum all entries in column G for June 2007 and PVC & Pipe. Biff "BEEM" wrote in message ... Thank you for your advice. I am using Excel 2000 and thought I could use sumproduct , text after looking at quite a few questions on the community. I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. Thank you again for advice. Roger Govier" wrote: Hi You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#7
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
Hi
Biff is quite right, it can be done with Sumproduct formulae, I just happen to prefer Pivot Tables. If you amend Biff's formula to =SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmmyyyy")=I$1), --($B$2:$B$1000=$H2),$G$2:$G$1000) and enter in I2 then you can copy across through J2:T2 and copy I2:T2 down for as many rows as you have entries in column H -- Regards Roger Govier "T. Valko" wrote in message ... I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. You can. Roger was describing one way to go about it. Here's a formula that will do what you want: Dates = column A Items = column B Amounts = column G =SUMPRODUCT(--(TEXT(A2:A1000,"mmmyyyy")="Jun2007"), --(B2:B1000="PVC & Pipe"),G2:G1000) That will sum all entries in column G for June 2007 and PVC & Pipe. Biff "BEEM" wrote in message ... Thank you for your advice. I am using Excel 2000 and thought I could use sumproduct , text after looking at quite a few questions on the community. I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. Thank you again for advice. Roger Govier" wrote: Hi You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#8
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
Thank you so very much it is much appreciated to find a solution.
-- BEEM "Roger Govier" wrote: Hi Biff is quite right, it can be done with Sumproduct formulae, I just happen to prefer Pivot Tables. If you amend Biff's formula to =SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmmyyyy")=I$1), --($B$2:$B$1000=$H2),$G$2:$G$1000) and enter in I2 then you can copy across through J2:T2 and copy I2:T2 down for as many rows as you have entries in column H -- Regards Roger Govier "T. Valko" wrote in message ... I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. You can. Roger was describing one way to go about it. Here's a formula that will do what you want: Dates = column A Items = column B Amounts = column G =SUMPRODUCT(--(TEXT(A2:A1000,"mmmyyyy")="Jun2007"), --(B2:B1000="PVC & Pipe"),G2:G1000) That will sum all entries in column G for June 2007 and PVC & Pipe. Biff "BEEM" wrote in message ... Thank you for your advice. I am using Excel 2000 and thought I could use sumproduct , text after looking at quite a few questions on the community. I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. Thank you again for advice. Roger Govier" wrote: Hi You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#9
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
Thank you so very much for your solution.
-- BEEM "T. Valko" wrote: I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. You can. Roger was describing one way to go about it. Here's a formula that will do what you want: Dates = column A Items = column B Amounts = column G =SUMPRODUCT(--(TEXT(A2:A1000,"mmmyyyy")="Jun2007"), --(B2:B1000="PVC & Pipe"),G2:G1000) That will sum all entries in column G for June 2007 and PVC & Pipe. Biff "BEEM" wrote in message ... Thank you for your advice. I am using Excel 2000 and thought I could use sumproduct , text after looking at quite a few questions on the community. I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. Thank you again for advice. Roger Govier" wrote: Hi You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
#10
|
|||
|
|||
SUMPRODUCT AND TEXT INM FORMULA
lHello Roger
I tried your formula and all I get is zeroes in the cells. Where you have I$1 this is where I have 01/07/2006 and have formatted in mmm/yyyy. I have copied your fomula from I2:T2 and all I get is zeroes. What am I doing wrong ? I have formatted coulmn A dd/mm/yy and I also tried dd/mm/yyyy, dd/mmm/yyyy Can you please help as I cannot find the solution. Thank you. -- BEEM "Roger Govier" wrote: Hi Biff is quite right, it can be done with Sumproduct formulae, I just happen to prefer Pivot Tables. If you amend Biff's formula to =SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmmyyyy")=I$1), --($B$2:$B$1000=$H2),$G$2:$G$1000) and enter in I2 then you can copy across through J2:T2 and copy I2:T2 down for as many rows as you have entries in column H -- Regards Roger Govier "T. Valko" wrote in message ... I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. You can. Roger was describing one way to go about it. Here's a formula that will do what you want: Dates = column A Items = column B Amounts = column G =SUMPRODUCT(--(TEXT(A2:A1000,"mmmyyyy")="Jun2007"), --(B2:B1000="PVC & Pipe"),G2:G1000) That will sum all entries in column G for June 2007 and PVC & Pipe. Biff "BEEM" wrote in message ... Thank you for your advice. I am using Excel 2000 and thought I could use sumproduct , text after looking at quite a few questions on the community. I take it that what you are saying is that in excel 2000 I cannot get the cost of all the pvc & pipe I buy in a particular month from the entries I enter. Thank you again for advice. Roger Govier" wrote: Hi You would be better off using a Pivot Table for your report. If you are using XL2003, then place your cursor in any cell of your source data, and choose DataListCreate List This will create a dynamic list for you which will grow as you add more data. Then, DataPivot TableNextNextLayout In the PT skeleton report that appears, Drag Date to the row area Drag Item to the row area Drag Net Amount to the data area Click OKFinish On the new report sheet that appears, right click on DateGroup and Show DetailGroupselect Month and YearOK Now drag new Year field that appears to the Page area Drag Date and drop it on the word Total, and Month will become a Column field. You will now have your report and as more new items are added, they will create more rows in the report. If you are using XL2002 or earlier, then you will nor be able to create the List as outlined above. Instead, create a Dynamic Range, InsertNameDefine Name myData Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1)) In the stages as above DataPivot TableNextenter myTable in Source boxNextLayout Continue as for the remainder of the report setup. For more help on Pivot Tables, take a look at Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables -- Regards Roger Govier "BEEM" wrote in message news First of my apologies I did not mean to offend. I did not know that. I want to get a total from column g for all the PVC & PIPE that I paid for in the month of july and then have this figure entered in column i and in column h i would have a heading pvc & pipe, which is one row below i2 heading of jul 07,and underneath this heading h I have the other 14 supplies that I order and if necessary increase this list during the year. but have columns i to t remain as the months of the year does this make sense ? -- BEEM "ShaneDevenshire" wrote: Hi, First a little internet etiquette - when you type in all upper case you are SHOUTING. You have shown us a formula but you haven't told us what you want it to do. =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) -- Thanks, Shane Devenshire "BEEM" wrote: i HAVE A WORKSHEET WITH 8 COLUMNS COULMN A DATE COLUMN B ITEM COULMN C PAID COLUMN E PAYMENT COLUMN E TOTAL PAID COLUMN F TAX COLUMN G NET AMOUNT THESE COLUMNS ARE NOT SORTED EITHER IN DATE OR ITEM ORDER. I THEN HAVE IN COLUMN H ROW 2 A LIST OF THE SUPPLIES I USE AND THEN IN COLUMN I TO T THE MONTHS AND I HAVE FORMATTED THESE MONTHS AS 010707,010807,010907 ETC THEN CUSTOM YYMM AND IT SHOPWS JUL-06 AUG-06 ETC I AM THEN TRYING TO USE =SUMPRODUCT(--(TEXT$B$2:$B$1000,"PVC & PIPE")=TEXT($I$2,"yymm")),$G$1000) BUT I GET #VALUE! ERROR and I would then like to copy formula down and change "PVC & PIPE" to the other supplies "shower","tap" etc etc I use. CAN YOU ASSIST AS I CANNOT FIND WHAT I AM DOING WRONG AND HAVE SPENT A FEW HOURS TRYING DIFFERENT VARIATIONS AND STILL GET THE SAME ERROR MESSAGE. Thank you -- BEEM |
|
Thread Tools | |
Display Modes | |
|
|