Need help with a formula

  • Thread starter Thread starter dernspiker
  • Start date Start date
D

dernspiker

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*
 
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
 
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*
Click to expand...

= {SUM( IF(A2:Z1000 = "Unit 89900", B2:AA1000, "") )}
the sum range is shifted 1 column from search range
enter as array formula
 
Back
Top