You're very welcome. Thanks for the feedback letting us know it worked
out for you.

--
Regards

Roger Govier


"NILELATOR" wrote in message
...
thank you that worked.

"Roger Govier" wrote:

Hi

You need to have highlighted the Date field before trying to Group.
If it comes up with the message "Cannot group by that Selection",
then
either there are some invalid Dates within your date column, or there
are some blank cells. In either of these cases, Grouping cannot
occur.
Check your source data.

--
Regards

Roger Govier


"NILELATOR" wrote in message
...
Hi
i have tried the pivot table and went through your steps but when i
try to
change the date field from mon, date, yr to only month it is not
letting me
do it. when i use the pt toolbar drop down to selectgroup and show
detailgroupmonth. it stops at group and does not show any more
selections
after that?

"Roger Govier" wrote:

Hi

If your names are in column A and Dates in column B and value to
be
summed is in column E,
then if you place the Name you are seeking in A5 and the month
number
in
C5

=SUMPRODUCT(($A$7:$A$1000=$A$5)*
(MONTH($B$7:$B$1000)=$C$5)*($E$7:$E$1000))

But you would be far better off with a Pivot Table. I assume you
have
a
header row in row 6, with titles for each column
First create a Dynamic Range.
InsertNameDefineName Mydata Refers to
=OFFSET($A$6,0,0,COUNTA($A$7:$A$1000),9)

Place your cursor in row 6, DataPivot TableNextSource
=MydataFinish
On the new sheet created,
Drag the Date field to the Row area
From the PT toolbar, use the dropdown to select Group and Show
DetailGroupMonth
Then drag the newly grouped Date field to the Column area instead.
Drag the Name field to the Row Area
Drag the Amount field to the Data area.
Drag your filed heading for column H to the Page Area

Now you can select Pend or No or Yes from the dropdown on Page
field
and
see the data summarised by Name and by Month.

For more information on Pivot Tables take a look at the following
sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"NILELATOR" wrote in message
...
Roger, you were right in the way i wanted to use this formula,
thank
you. i
have one more problem. in the same speadsheet and using the same
columns ("A"
name) i now need to count the rows by month which is located in
column
("B"
bid date) and formatted as (01/10/06), in column "K" i have
(=If(A70,Month(A7),"") )which returns the month value as "1" so
i
could use
countif to total all bids now i need to total by "name" . i have
tried
to use
the same formula that you gave subsitiuting sumproduct for
countif.
but i
can't make it work.


"Roger Govier" wrote:

Hi

what does the "*" inbetween the formulas do?
There are 2 sets of tests being carried out and 1 set of values
resulting in 3 arrays.
Does column A = value in C5, True or False
Does column H contain "Pend", True or False
And the data in column E

The "*" is use to multiply the ranges together, which coerces
the
True's
to 1's and False's to 0's so when the 3 arrays are multiplied
together
you get something like
0 x 1 x 50 = 0
1 x 0 x 25 = 0
1 x 1 x 30 = 30
and so on down the 994 rows in the range 7:1000.
Sumproduct then adds all these values to give the answer. (in
this
small
example, 30)

The alternative is to use the double unary minus "--" to coerce
the
True/False to 1/0 and just use a "," between each of the sets.
This was the method Biff did in his later posting.

Biff also had a different take on your request, and has given a
solution
which returns a True if column H contains either "Pend" or
"Yes"
or
"No"
whereas I had read it that you would want to run all 3
separately
and
have the totals for each case by Changing "Pend" to Yes or No
in
different formulae.
--(ISNUMBER(MATCH(H7:H17,{"pend","yes","no"},0)))
where if Match returns a number for any of the three values in
the
array
{ }, then it returns a 1.
--
Regards

Roger Govier


"NILELATOR" wrote in
message
...
Thank you that works, I didn't know about the caps ment
shouting,
i'm
getting
old and it helps to see.
what does the "*" inbetween the formulas do?

"Roger Govier" wrote:

Hi

Try
=SUMPRODUCT(($A$7:$A$1000=$C$5)*($H$7:$H$1000="PEN D")*($E$7:$E$1000))

For your future reference
Please do not post in capitals, it is regarded as "shouting"
in
the
Newsgroups, and is very difficult to read.

--
Regards

Roger Govier


"NILELATOR" wrote in
message
...
I HAVE A SPREAD SHEET WITH 9 COLUMNS OF DIFFERENT
INFORMATION
BUT
SPECIFIC TO
EACH ROW, STARTING WITH ROW 7. I NEED TO TOTAL ALL ROWS OF
COLUMN E
(THE
VALUE) , BASED ON MATCH WITH CELL C5 (LIST OF NAMES) TO
ANY
MATCHING
ROW IN
COLUMN A (NAMES), BUT ALSO NEEDS TO MATCH EITHER "PEND",
"yES",
"NO"
IN
COLUMN H.
IT NEEDS TO ONLY COUNT CELLS IN COLUMN E, IF BOTH
CRITERIA'S
IN
COLUMN
A AND
H HAS BEEN MET