function

i am not sure how to do this. I have a narray of numbers
in colum a (a4:a121) of either 1 or 2. In colum f (f4:f121)
is the same array. I would like a formula that will look
across rows and compare i.e A4 and F6 and tell me if they
are both a number one. I would like to display the sum of
all the rows A AND F that display the number "1" only.

=SUMPRODUCT((A4:A121=1)*(F4:F121=1))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



wrote in message
...
i am not sure how to do this. I have a narray of numbers
in colum a (a4:a121) of either 1 or 2. In colum f (f4:f121)
is the same array. I would like a formula that will look
across rows and compare i.e A4 and F6 and tell me if they
are both a number one. I would like to display the sum of
all the rows A AND F that display the number "1" only.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

I am not sure what column your total is in that you want
to sum. I assumed you had a total on each row in column G
and here is a formula that might work for you:
=SUM(IF(AND(A4=1,AND(F4=1)),G4))


-----Original Message-----
i am not sure how to do this. I have a narray of numbers
in colum a (a4:a121) of either 1 or 2. In colum f
(f4:f121)
is the same array. I would like a formula that will look
across rows and compare i.e A4 and F6 and tell me if they
are both a number one. I would like to display the sum of
all the rows A AND F that display the number "1" only.
.

If there are no blank rows involved you can use something like

=OFFSET(A1,COUNTA(A:A)-1,)

if blanks can be involved

=INDEX(A:A,MAX((A1:A1000"")*ROW(INDIRECT("1:1000 "))))

entered with ctrl + shift & enter (change the size if you expect more than
1000 rows)

--

Regards,

Peo Sjoblom


"excelblack" wrote in message
...
Hi,

I'd like to know if there is a function that can give me the last valid
result in a list. It can be in VBA.

Thank's



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

"Valid" can mean many things. Try:

=INDEX(A1:A20,MAX(IF(LEN(A1:A20)0,ROW(A1:A20))))

Array-entered, meaning press ctrl/shift/enter. Change the
range to meet your needs.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi,

I'd like to know if there is a function that can give me
the last valid
result in a list. It can be in VBA.

Thank's



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.