Totaling Columns of the same type

kcolls · Apr 15th, 2002, 02:16 PM

I want to total columns of the same type that are on a different worksheet and they are B3, B13, B23, B33, B43....... because
B?3 are always going to be one type.

I would think something like =sum(B*3)

Is this possible??

Please help! Thanks

Big Blue · Apr 15th, 2002, 02:22 PM

do you want to "total the columns" individually, or are you trying to SUM them all together?

is there a set number of B_3 columns? if so, maybe you could name them as ranges....

kcolls · Apr 15th, 2002, 02:56 PM

There is not a set range of B_3 but I want a complete total of all B_3's any other idea?

Aladin Akyurek · Apr 15th, 2002, 04:13 PM

=SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))

But, why not simply

=SUM(B3,B13,B23) ?

kcolls · Apr 16th, 2002, 06:02 AM

That my be the answer but it is B_3 to the infinate number to typing/clicking up to B1993 eventually may not be enough?

Aladin Akyurek · Apr 16th, 2002, 09:42 AM

Quote:

On 2002-04-16 05:02, kcolls wrote:
That my be the answer but it is B_3 to the infinate number to typing/clicking up to B1993 eventually may not be enough?

You will use the SUMPRODUCT formula, I guess.

Aladin

Yogi Anand · Apr 16th, 2002, 10:37 AM

Quote:

On 2002-04-15 15:13, Aladin Akyurek wrote:
=SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))

Hi Aladin:
Why not simply
=SUMPRODUCT((RIGHT(B3:B23)="3")*(B3:B23))
after all it is only a string we are dealing with at that point
Please advise if there is a specific advantage to converting "3" into a numeric digit with "3"+0
Your insightful solutions are always a delight!

Aladin Akyurek · Apr 16th, 2002, 12:15 PM

Quote:

On 2002-04-16 09:37, Yogi Anand wrote:

Quote:

On 2002-04-15 15:13, Aladin Akyurek wrote:
=SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))

Hi Aladin:
Why not simply
=SUMPRODUCT((RIGHT(B3:B23)="3")*(B3:B23))
after all it is only a string we are dealing with at that point
Please advise if there is a specific advantage to converting "3" into a numeric digit with "3"+0
Your insightful solutions are always a delight!

Just a precaution in case 3 is entered as a number in a cell of its own, somthing that I'd recommend. Then, it is easier to change

=SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))

to

=SUMPRODUCT((RIGHT(ROW(B3:B23))+0=A1)*(B3:B23))

where A1 houses 3.

Hope this sounds convincing

.

Aladin

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

Apr 15th, 2002, 02:16 PM	#1
kcolls New Member Join Date: Apr 2002 Posts: 3	I want to total columns of the same type that are on a different worksheet and they are B3, B13, B23, B33, B43....... because B?3 are always going to be one type. I would think something like =sum(B*3) Is this possible?? Please help! Thanks

Apr 15th, 2002, 02:22 PM	#2
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	do you want to "total the columns" individually, or are you trying to SUM them all together? is there a set number of B_3 columns? if so, maybe you could name them as ranges....

Apr 15th, 2002, 02:56 PM	#3
kcolls New Member Join Date: Apr 2002 Posts: 3	There is not a set range of B_3 but I want a complete total of all B_3's any other idea?

Apr 15th, 2002, 04:13 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	=SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23)) But, why not simply =SUM(B3,B13,B23) ?

Apr 16th, 2002, 06:02 AM	#5
kcolls New Member Join Date: Apr 2002 Posts: 3	That my be the answer but it is B_3 to the infinate number to typing/clicking up to B1993 eventually may not be enough?