Alternating Columns

[Brian from Maui]

Aloha All!

I have text and data in alternating columns.
Is there a formula such as SUMIF to search for a criteria in columns with text and sum the next cell to it with data? The range and sum range are identical. Is this possible?

Brian

 

[Paul-Johnson]

You want to use an array formula. They are pretty easy.

Search the web site for CSE formulas. There is a great write up and examples

 

[Paul B]

=SUMIF(F2:F230,"data",E2:E230) Sums E2:E230 if data is in F2:F230

 

[Guest]

Aloha!

Here's a short sample of the text and data

A B C D E F G H
ot 4 st 8 dt 4 ot 4

I would like to search for ot and sum 4 under columns B and H.

Brian

 

[Aladin Akyurek]

On 2002-02-23 19:05, Anonymous wrote:
Aloha!

Here's a short sample of the text and data

A B C D E F G H
ot 4 st 8 dt 4 ot 4

I would like to search for ot and sum 4 under columns B and H.

Brian

Aloha,

Come on Brian -- you should know by now the syntax of SUMIF

Thus, either

=SUMIF(A:A,"ot",B:B)+SUMIF(G:G,"ot",H:H)

or, using fixed ranges,

=SUMIF(A2:A10,"ot",B2:B10)+SUMIF(G2:G10,"ot",H2:H10)

There is no way you can get rid of +. That is, a single SUMIF won't do.

Aladin

 

[Brian from Maui]

Aladin,

I thought they'd be a shorter way! I have 15 columns of text and 15 columns of data by about a 100 rows. I guess I'll have to reformat the spreadsheet or do the + 15 times. As always Aladin, much Mahalos!!

Brian