SUMPRODUCT AND TEXT INM FORMULA

[BEEM]

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

 

[ShaneDevenshire]

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)

 

[BEEM]

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 ?

 

[Roger Govier]

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 Data>List>Create List
This will create a dynamic list for you which will grow as you add more
data.
Then, Data>Pivot Table>Next>Next>Layout
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 OK>Finish

On the new report sheet that appears, right click on Date>Group and Show
Detail>Group>select Month and Year>OK
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,
Insert>Name>Define
Name myData
Refers to =OFFSET($A$A1,0,0,COUNTA($A:$A),COUNTA($1:$1))
In the stages as above
Data>Pivot Table>Next>enter myTable in Source box>Next>Layout
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

 

[BEEM]

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.

 

[T. Valko]

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

 

[Roger Govier]

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

 

[BEEM]

Thank you so very much it is much appreciated to find a solution.

 

[BEEM]

Thank you so very much for your solution.

 

[BEEM]

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.

 

[Roger Govier]

My Apologies

I should have typed
=SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmmyyyy")=TEXT(I$1,"mmmyyyy"),
--($B$2:$B$1000=$H2),$G$2:$G$1000)

 

[BEEM]

Thank you for your prompt answer.
I have tried your formula and I get the error message
"The formula you typed contains an error"
I cannot enter the formula.
I have trieed several variations but cannot get it to work
What am I doing wrong ?
I have checked all my entries and have entered the same as on your post
Sorry to trouble you

 

[Roger Govier]

Sorry, my eyesight is getting worse. I could have sworn that I typed 2
parentheses at the end of that first line.

Try
=SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmmyyyy")=TEXT(I$1,"mmmyyyy")),
--($B$2:$B$1000=$H2),$G$2:$G$1000)

 

[BEEM]

Thank you so very much this has now fixed problem.
Thank you for all your help. It is very much appreciated.