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