Alternating Columns

Brian from Maui · Feb 23rd, 2002, 07:53 PM

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 · Feb 23rd, 2002, 07:56 PM

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 · Feb 23rd, 2002, 07:57 PM

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

Feb 23rd, 2002, 08:05 PM

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 · Feb 24th, 2002, 01:56 AM

Quote:

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 · Feb 24th, 2002, 06:54 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 23rd, 2002, 07:53 PM	#1
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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

Feb 23rd, 2002, 07:56 PM	#2
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	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

Feb 23rd, 2002, 07:57 PM	#3
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	=SUMIF(F2:F230,"data",E2:E230) Sums E2:E230 if data is in F2:F230

Feb 23rd, 2002, 08:05 PM	#4
Guest Posts: n/a	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

Feb 24th, 2002, 06:54 AM	#6
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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