Looking to easily sum up the total per room

[DavidPonnet]

Hello,

I will try to explain the following problem as good as possible.

In the first column, there is the name of a room combined with the number to a case (fe. R01-V02 / R05-V01 etc )

Sometimes more then 1 case per room.

I would like to make totals for each room, based on the room number (so part of the information.) R01 / R02 etc

Would it be possible to easily make such a formula ? (I need to have the sum of different columns)

[TABLE="width: 500"]
<tbody>[TR]
[TD]R01-V01[/TD]
[TD]5000 €[/TD]
[TD]250 h[/TD]
[TD]100 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R01-V02[/TD]
[TD]7500 €[/TD]
[TD]300 h[/TD]
[TD]125 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R02-V01[/TD]
[TD]1200 €[/TD]
[TD]100 h[/TD]
[TD]50 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R02-V02[/TD]
[TD]1500 €[/TD]
[TD]125 h[/TD]
[TD]60 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R02-V03[/TD]
[TD]2500 €[/TD]
[TD]180 h[/TD]
[TD]100 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sitewolf]

Hello,

I will try to explain the following problem as good as possible.

In the first column, there is the name of a room combined with the number to a case (fe. R01-V02 / R05-V01 etc )

Sometimes more then 1 case per room.

I would like to make totals for each room, based on the room number (so part of the information.) R01 / R02 etc

Would it be possible to easily make such a formula ? (I need to have the sum of different columns)

[TABLE="width: 500"]
<tbody>[TR]
[TD]R01-V01[/TD]
[TD]5000 €[/TD]
[TD]250 h[/TD]
[TD]100 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R01-V02[/TD]
[TD]7500 €[/TD]
[TD]300 h[/TD]
[TD]125 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R02-V01[/TD]
[TD]1200 €[/TD]
[TD]100 h[/TD]
[TD]50 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R02-V02[/TD]
[TD]1500 €[/TD]
[TD]125 h[/TD]
[TD]60 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R02-V03[/TD]
[TD]2500 €[/TD]
[TD]180 h[/TD]
[TD]100 h[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Maybe I have this backwards, but are you talking about summing each unit column by room? i.e. 3rd column total for R01 is 225?
If so, could you not have R01, R02 etc in a list/table then combine left function with sumif?

 

[DavidPonnet]

Maybe I have this backwards, but are you talking about summing each unit column by room? i.e. 3rd column total for R01 is 225?
If so, could you not have R01, R02 etc in a list/table then combine left function with sumif?

That is correct, but the left colum cannot be changed. Is it possble to have a "sumif" formula with only part of a cell value ?

 

[sitewolf]

That is correct, but the left colum cannot be changed. Is it possble to have a "sumif" formula with only part of a cell value ?

I was thinking so when I typed this, but now not thinking how........without adding a column.
Can you add a column beside the room column, do left(A1,3) to get room only in that new column, then use that for your sumif?

 

[pkdeimos]

Hi,

Assuming your data is in A1:D5 and the list of rooms (R01, R02... ) is in H1:H5, try this formula in I1 for sum of column B for R01

=SUMIF($A$1:$A$5,"="&$H1&"*",B$1:B$5)

This will return I1=12500. Copy the same for other cells.

Hope this helps.
Regards.

 

[DavidPonnet]

Hi,

Assuming your data is in A1:D5 and the list of rooms (R01, R02... ) is in H1:H5, try this formula in I1 for sum of column B for R01

=SUMIF($A$1:$A$5,"="&$H1&"*",B$1:B$5)

This will return I1=12500. Copy the same for other cells.

Hope this helps.
Regards.

that worked ! thx !!!