With this, the "B" can be the first OR second character in F5 to AJ5:

=SUMPRODUCT(((ISNUMBER(SEARCH("B",F5:AJ5)))*(LEN(F 5:AJ5)=2)*F8:AJ8/2)+(F5:AJ5="B")*F8:AJ8)

With this, the "B" MUST be the FIRST character:

=SUMPRODUCT(((F5:AJ5="B")*F8:AJ8)+(LEFT(F5:AJ5)="B ")*(LEN(F5:AJ5)1)*(F8:AJ8)/2)


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"UKMAN" wrote in message
...
sorry for slow reply but not been able to log on.

The range of variables for "B" would be B, B2, BH, BM, BT, BS or BX. sorry
but so many budgets...)
Where it is a double character the value would be divide in 2 for that
element

Sorry if this sounds complicated but I suppose all I am trying to do is
expand your orginal formula so that where there are 2 charaters in a
specific
field that specific value is divided by 2.

Variable B, B2, BH, B
Value 2 2 4 3
Result 2 1 2 3

The above would result in the total of 8 for the varaible B and 2 for the
variable H. (B2 just says that that value has to be divided in 2)



"RagDyer" wrote:

Are you saying that the variable could be either "BS" and/or "SB"?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"UKMAN" wrote in message
...
Where you guys get this short code :) many thanks I am learning :)

Yep I think you are nearly there... :)

In all there are 8 budgets all identified by a single character (i.e.
"B")
and 13 instances of where budgets have a variable (i.e. "BS") to
identify
a
shared value. Hense why I take you magic code and slightly adjust it
depending on what variable the cell is trying to calculate.

Where a variable is a single character i.e. "B" then they would be
allocated
the full value i.e. £2.

If the variable is 2 characters i.e. "BS" then the value i.e. £2 would
be
split in half between the 2 budgets i.e. £1 for "B" and £1 for "S".
Below
won't work but maybe help to confirm...:)

a cell calculating the vales for budget "B" would have a formula
SUMIF((F$5:AJ$5,"B2",F$8:AJ$8)/2)+SUMIF(F$5:AJ$5,"B",F$8:AJ$8)+SUMIF((F$5:AJ$5,"B S",F$8:AJ$8)/2)


many thanks



"RagDyer" wrote:

First of all, the formula that you're using can be revised to this:

=SUM(SUMIF(F5:AJ5,{"B","B2","BH","BM","BT","BS","B X"},F8:AJ8))

Next, I don't exactly follow what you're now looking to do.

Are you saying that with the above variables, you want to split the
values
wherever there are more then a single letter?

You want all of "B" and half of B2, BH, BM, BT, BS, and BX to be
totaled
for B?

And you want half totals, individually, for 2, H, M, T, S, and X?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"UKMAN" wrote in message
...
Hi

I swoped heads and used my brain to reread your work and it
worked....

One question now on how I have adaptored your work :)
=SUMIF(F$5:AJ$5,"B",F$8:AJ$8)+SUMIF(F$5:AJ$5,"B2", F$8:AJ$8)+SUMIF(F$5:AJ$5,"BH",F$8:AJ$8)+SUMIF(F$5: AJ$5,"BM",F$8:AJ$8)+SUMIF(F$5:AJ$5,"BT",F$8:AJ$8)+ SUMIF(F$5:AJ$5,"BS",F$8:AJ$8)+SUMIF(F$5:AJ$5,"BX", F$8:AJ$8)

This is the formula gives me the total of the costs for certain
variables.
What I need to do know is that where there is 2 charactors in the
variable
i.e. "B2" or "BS" etc I need to split that piece of addition in half.
Reason
being is that the cost is split between 2 budgets. That make sence?

In other areas I use a countif but of course no good in this case.

As ever many thanks.
"RagDyeR" wrote:

So, I then don't understand your post from an hour ago!

It gave the connotation of things *not* working out for you, so
that's
why I
asked for the locations of the variables, which I now assume you
understood
and have well in hand.

Glad you got it all together.<g
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit
!
-----------------------------------------------------------------------------------------------

"UKMAN" wrote in message
...
RagDyeR,

It worked brilliantly my utmost grats...

Cheers

"RagDyeR" wrote:

The formula I suggested will do that *exactly*, however, in a
vertical
configuration.

It can very easily be revised to a row (horizontal), but the point
now
comes
up ... do you have a pre-existing list of your variables to use as
a
criteria for the Countif() formula?
OR
Do you also want (need) a formula to determine a list of your
unique
variables?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may
benefit
!
-----------------------------------------------------------------------------------------------



"UKMAN" wrote in message
...
RagDyer, I think I understand your brilliance... but I think I may
need
to
give another example to clarify. sorry.

Imagine a calendar with its 31 days (a3-ae3) horizontal showing 1
of
10
variables i.e B, B.5, T
In row a4-ae4 with be a changing cost i.e £2.50 for the above
cell.

1
2 a b c d e f g
3 B T B B B.5 B.5 B
4 2.5 2.0 1.8 2.0 1.25 1 3.0

What I am trying to do is calculate in cells ah4, ai4 etc is total
sum
of
the costs for each the variables i.e. in the above exampl B=9.3,
T=2,
B.5=2.25

I hope this example shows it more clearly and many thanks.


"RagDyeR" wrote:

Say your data is in A1 to AE2.

List your variables in say A5 to A14.

In B5, enter this formula:

=SUMIF(A$1:AE$1,A5,A$2:AE$2)

And copy down to B14.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===


"UKMAN" wrote in message
...
Hi
I have 2 rows of data (31 columns wide), in each cell of the the
top
row
is
a variable i.e. B, B.5, P,; (upto 10 differant variables).
In each cell of the 2nd row under each variable will be an
associated
cost
i.e. £2.00, £1.50 or whatever.

What I need to do is calculate the total sum of the individual
variables,
i.e. sum of the B variable = 10 occurances x £2.00 = £20.00 etc
and
this
is
shown in a seperate area of the spreadsheet.

gratefull for all and any help.
Cheers