A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SUMPRODUCT AND TEXT INM FORMULA



 
 
Thread Tools Display Modes
  #1  
Old June 16th, 2007, 04:49 AM posted to microsoft.public.excel.worksheet.functions
BEEM
external usenet poster
 
Posts: 8
Default 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  
Old June 16th, 2007, 05:39 AM posted to microsoft.public.excel.worksheet.functions
ShaneDevenshire
external usenet poster
 
Posts: 2,232
Default 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  
Old June 16th, 2007, 06:03 AM posted to microsoft.public.excel.worksheet.functions
BEEM
external usenet poster
 
Posts: 8
Default 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  
Old June 16th, 2007, 09:24 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,602
Default 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  
Old June 17th, 2007, 04:00 AM posted to microsoft.public.excel.worksheet.functions
BEEM
external usenet poster
 
Posts: 8
Default 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  
Old June 17th, 2007, 04:30 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old June 17th, 2007, 12:20 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,602
Default 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  
Old June 18th, 2007, 09:22 AM posted to microsoft.public.excel.worksheet.functions
BEEM
external usenet poster
 
Posts: 8
Default 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  
Old June 18th, 2007, 09:22 AM posted to microsoft.public.excel.worksheet.functions
BEEM
external usenet poster
 
Posts: 8
Default 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  
Old June 19th, 2007, 10:21 AM posted to microsoft.public.excel.worksheet.functions
BEEM
external usenet poster
 
Posts: 8
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.