Formula Help
 

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)


Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

Formula Help
 

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson

Formula Help
 

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson

Formula Help
 

Thank you Dave, but unfortunately I have code numbers 102, 104 and this
formula added the data assigned to them

Any other suggestions?

Thanks in advance

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson

Formula Help
 

Yes it did.
Is there another way around it?


"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson

Formula Help
 

Maybe...

=SUM((LEFT(F9:AW28,2)="10")*IF(ISNUMBER(B9:AS28),B 9:AS28,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will treat the 10FA as 0.



Judy L wrote:

Yes it did.
Is there another way around it?

"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson


--

Dave Peterson

Formula Help
 

Thanks again Dave, but I have recieved an error "Array formulas are not valid
in merged cells"

Any other ideas?

"Dave Peterson" wrote:

Maybe...

=SUM((LEFT(F9:AW28,2)="10")*IF(ISNUMBER(B9:AS28),B 9:AS28,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will treat the 10FA as 0.



Judy L wrote:

Yes it did.
Is there another way around it?

"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson


--

Dave Peterson

Formula Help
 

Don't use merged cells?

Judy L wrote:

Thanks again Dave, but I have recieved an error "Array formulas are not valid
in merged cells"

Any other ideas?

"Dave Peterson" wrote:

Maybe...

=SUM((LEFT(F9:AW28,2)="10")*IF(ISNUMBER(B9:AS28),B 9:AS28,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will treat the 10FA as 0.



Judy L wrote:

Yes it did.
Is there another way around it?

"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Formula Help
 

Or remove the merging.
Add the array formula
re-merge the cells

Dave Peterson wrote:

Don't use merged cells?

Judy L wrote:

Thanks again Dave, but I have recieved an error "Array formulas are not valid
in merged cells"

Any other ideas?

"Dave Peterson" wrote:

Maybe...

=SUM((LEFT(F9:AW28,2)="10")*IF(ISNUMBER(B9:AS28),B 9:AS28,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will treat the 10FA as 0.



Judy L wrote:

Yes it did.
Is there another way around it?

"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Formula Help
 

This is linked to other sheets. Is there no other way? (please)

Thanks again

"Dave Peterson" wrote:

Or remove the merging.
Add the array formula
re-merge the cells

Dave Peterson wrote:

Don't use merged cells?

Judy L wrote:

Thanks again Dave, but I have recieved an error "Array formulas are not valid
in merged cells"

Any other ideas?

"Dave Peterson" wrote:

Maybe...

=SUM((LEFT(F9:AW28,2)="10")*IF(ISNUMBER(B9:AS28),B 9:AS28,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will treat the 10FA as 0.



Judy L wrote:

Yes it did.
Is there another way around it?

"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Formula Help
 

Why didn't the last suggestion work?

Judy L wrote:

This is linked to other sheets. Is there no other way? (please)

Thanks again

"Dave Peterson" wrote:

Or remove the merging.
Add the array formula
re-merge the cells

Dave Peterson wrote:

Don't use merged cells?

Judy L wrote:

Thanks again Dave, but I have recieved an error "Array formulas are not valid
in merged cells"

Any other ideas?

"Dave Peterson" wrote:

Maybe...

=SUM((LEFT(F9:AW28,2)="10")*IF(ISNUMBER(B9:AS28),B 9:AS28,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will treat the 10FA as 0.



Judy L wrote:

Yes it did.
Is there another way around it?

"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Formula Help
 

Try this user-defined function. Copy & paste this code into a VBA module in
your workbook:

Public Function GetTotal(MyCode As Integer, Target As Range, _
ColDiff As Integer) As Double
Dim c As Range, NbrOut As Double
NbrOut = 0
For Each c In Target
If Val(c.Value) = MyCode Then
If IsNumeric(c.Offset(0, ColDiff).Value) Then
NbrOut = NbrOut + c.Offset(0, ColDiff).Value
End If
End If
DoEvents
Next c
GetTotal = NbrOut
End Function

For the code 10, you would call it like this:
=GetTotal(10,$F$9:$AW$28,-4)

-4 is how many columns offset the function should go to find the value if it
finds the code in a cell (negative number = to the left, positive = to the
right). In this case,
-4 will cause it to retrieve values from the range B9:AS28 based on testing
the range F9:AW28.

For the code 102, you would call it like this:
=GetTotal(102,$F$9:$AW$28,-4)

Merged cells shouldn't bother it. If you are new to user-defined functions
(macros), this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"Judy L" wrote:

Yes it did.
Is there another way around it?


"Tom Hutchins" wrote:

I don't think that will work because the range she is testing is included in
much of the range returning values, and now the codes are text instead of
numbers. Having "10FA" or "636D" inthe B9:AS28 range will cause a #VALUE
error, won't it?

Hutch

"Dave Peterson" wrote:

Maybe something like

=SUMPRODUCT((LEFT(F9:AW28,2)="10")*B9:AS28)

Judy L wrote:

Hello, I have a spreadsheet with the following formula modified for 50
different code numbers (ranging from 10 to 636)

=SUMPRODUCT((F9:AW28=10)*B9:AS28)
=SUMPRODUCT((F9:AW28=636)*B9:AS28)

Now I need to add 4 subcategories to these codes and be able to total them,
but still retain the original totals.

So my codes would become 10FA or 10C or 636D etc
Can I pick this info out of the sheet without messing up the original counts?

Any help would be greatly appreciated

Thank you

--

Dave Peterson