If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
function
=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 |
#3
|
|||
|
|||
function
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. . |
#4
|
|||
|
|||
Function
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/ |
#5
|
|||
|
|||
Function
"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/ . |
Thread Tools | |
Display Modes | |
|
|