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
|