Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a formula
Monthly I have a sheet that has columns of Accounting Units. They are spread
all over the sheet. What I need to do is add up the numbers next to those accounting units. Such as Unit 89900 maybe in Column A Row 1 and in Column E Row 5 and in Column Z Row 22. The important number is is the column next to those. So how can I write a formula where excel looks over they entire sheet and where ever it finds Unit 89900 it will add up the contents in the cell next to it? Sheesh I'm not even sure I can understand what I am asking... *sigh* |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a formula
Hi
assuming that all columns which have text have no values, and all columns with values have no text, then =SUMPRODUCT(--(A1:Y22="unit89900"),B1:Z22) Note ranges are of equal size, but offset by one column -- Regards Roger Govier "dernspiker" wrote in message ... Monthly I have a sheet that has columns of Accounting Units. They are spread all over the sheet. What I need to do is add up the numbers next to those accounting units. Such as Unit 89900 maybe in Column A Row 1 and in Column E Row 5 and in Column Z Row 22. The important number is is the column next to those. So how can I write a formula where excel looks over they entire sheet and where ever it finds Unit 89900 it will add up the contents in the cell next to it? Sheesh I'm not even sure I can understand what I am asking... *sigh* |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a formula
On Jul 1, 11:51*am, dernspiker
wrote: Monthly I have a sheet that has columns of Accounting Units. *They are spread all over the sheet. *What I need to do is add up the numbers next to those accounting units. *Such as Unit 89900 maybe in Column A Row 1 and in Column E Row 5 and in Column Z Row 22. *The important number is is the column next to those. So how can I write a formula where excel looks over they entire sheet and where ever it finds Unit 89900 it will add up the contents in the cell next to it? Sheesh I'm not even sure I can understand what I am asking... *sigh* = {SUM( IF(A2:Z1000 = "Unit 89900", B2:AA1000, "") )} the sum range is shifted 1 column from search range enter as array formula |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|