sum a value depending on the value of a cell??

U

UKMAN

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
 
M

Max

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
 
R

RagDyeR

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!
=====================================================


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
 
U

UKMAN

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

UKMAN

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

RagDyeR

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



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

Max

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

UKMAN

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
 
U

UKMAN

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
 
R

RagDyeR

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

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
 
R

RagDyeR

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

RagDyeR,

It worked brilliantly my utmost grats...

Cheers
 
U

UKMAN

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

RagDyer

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

=SUM(SUMIF(F5:AJ5,{"B","B2","BH","BM","BT","BS","BX"},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?
 
U

UKMAN

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,"BS",F$8:AJ$8)/2)


many thanks
 
U

UKMAN

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)
 
R

RagDyeR

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

=SUMPRODUCT(((ISNUMBER(SEARCH("B",F5:AJ5)))*(LEN(F5: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!
=====================================================

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

relective formula 0
Two Variable Lookup with approximate ranges 12
how to sum this easily...? 4
Help on complex multi-column sum is appreciated 0
sum cells to reach a specific value and return corresponding value 0
Sum Number of Rows based on Value in Another Cell 2
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Can I made a cell reference variable? i.e. =sum(D4:D(4+A2)) 1

Top