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

One interp & a way via index/match
Assuming the 2 reference rows are B1:AF2
you could use something like this, in say,
D6: =INDEX($B$2:$AF$2,MATCH(B6,$B$1:$AF$1,0))*C6
where in B6 is eg: B, & in C6 is the number/amt: 10
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"UKMAN" wrote:
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

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

Max, 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.


"Max" wrote:

One interp & a way via index/match
Assuming the 2 reference rows are B1:AF2
you could use something like this, in say,
D6: =INDEX($B$2:$AF$2,MATCH(B6,$B$1:$AF$1,0))*C6
where in B6 is eg: B, & in C6 is the number/amt: 10
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"UKMAN" wrote:
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

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

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

This set-up should deliver all of it for you ..

In A5:
=IF(A3="","",IF(COUNTIF($A$3:A3,A3)1,"",COLUMNS($ A:A)))
Copy A5 to AE5

In AH4:
=IF(COLUMNS($A:A)COUNT($A$5:$AE$5),"",INDEX($A$3: $AE$3,SMALL($A$5:$AE$5,COLUMNS($A:A))))

In AH5:
=IF(AH3="","",SUMIF($A$3:$AE$3,AH3,$A$4:$AE$4))
Select AH4:AH5, copy across by 31* cols to BL5, to cover the max possible
extent.

You will get the required results in AH4:BL5, ie the unique listing of the
variables in AH4:BL4 (with results neatly bunched to the left), and the
corresponding sums for each variable below.

*if as you say, there are only a max of 10 possible variables, then just
copy across by 10 cols to AQ5 will do.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"UKMAN" wrote:
Max, 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,

The variable for row 3 could be predetermined as there would be a maximum of
10. However the value of row 4 however cannot.

Again many thanks

"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

Max,

Sorry did as you said and didn't quite work out. if you email me at ukman1
at hotmail com then I can send you an example if that helps?

cheers


"Max" wrote:

This set-up should deliver all of it for you ..

In A5:
=IF(A3="","",IF(COUNTIF($A$3:A3,A3)1,"",COLUMNS($ A:A)))
Copy A5 to AE5

In AH4:
=IF(COLUMNS($A:A)COUNT($A$5:$AE$5),"",INDEX($A$3: $AE$3,SMALL($A$5:$AE$5,COLUMNS($A:A))))

In AH5:
=IF(AH3="","",SUMIF($A$3:$AE$3,AH3,$A$4:$AE$4))
Select AH4:AH5, copy across by 31* cols to BL5, to cover the max possible
extent.

You will get the required results in AH4:BL5, ie the unique listing of the
variables in AH4:BL4 (with results neatly bunched to the left), and the
corresponding sums for each variable below.

*if as you say, there are only a max of 10 possible variables, then just
copy across by 10 cols to AQ5 will do.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"UKMAN" wrote:
Max, 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,

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

SO ... where *exactly* are you listing your variables that are in Row3?

Don't you understand ... the Sumif() function must reference those
variables, either "hard coded" individually into the formula, or from a
pre-defined cell location.

In other words - you've got to put them in the formula ... somehow ! ! !

For me to do that for you, I've got to know where they are now, or where
they're going to be.

To repeat, if necessary, a unique list of these variables could be generated
by formulas, and then this *unique list* could be referenced in the Sumif()
formula.

What do you want to do?

This is really a simple problem that you're making complex by not providing
the necessary information!
--

Regards,

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

"UKMAN" wrote in message
...
RagDyeR,

The variable for row 3 could be predetermined as there would be a maximum of
10. However the value of row 4 however cannot.

Again many thanks

"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

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

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

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

Much better for all to keep discussions visible here ..

This sample demonstrates that the set-up works:
http://www.freefilehosting.net/download/3cbhh
Horiz Extract of Uniques List n Sumif.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"UKMAN" wrote:
Max,
Sorry did as you said and didn't quite work out. if you email me at ukman1
at hotmail com then I can send you an example if that helps?
cheers

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

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

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

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