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

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

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.

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.

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

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

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

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

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