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

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

Please turn off your caps lock!

Try this:

=SUMPRODUCT(--(A7:A17=C5),--(ISNUMBER(MATCH(H7:H17,{"pend","yes","no"},0))),E7 :E17)

Biff

"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

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

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

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

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

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

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

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

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