IF & AND question?

[StephenSLR]

Hi all,

In my excel sheet I am putting in quantities of apartments, there may be a few hundred apartments per complex.

Column A will be the unit no., column B number of bedrooms, column C no of bathrooms, etc.

e.g.
A , B, C,
101, 2, 1,
102, 1, 1,
103, 3, 2,

It may take 4 hours to clean a one bedder with one bathroom, 5 hours to clean a 2 bedroom, 1 bathroom unit and 6 hours to clean a 2 bedder with 2 bathrooms.

I would like a formula to calculate, if cell value A is 1 and cell value B is 1, spit out 4 in column D.

Of course this is a simplified version, my actual sheet will have about 6 columns and the units can be up to 4 bedrooms with 4 bathrooms and 3 balconies and all combinations in between from a studio apartment with no bedroom and 1 bathroom, no balcony, etc.

I know how to use IF & AND formulas for a small amount of variables but what is the best formula or technique to use for many variables without having to use a formula string a mile long?

Thanks in advance.

s

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try something like this

Create a table Bedrooms - Bathrooms - Hours (gray area)

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Unit​	Bedrooms​	Bathrooms​	Result​		Bedrooms​	Bathrooms​	Hours​
2​	101​	2​	1​	5​		1​	1​	4​
3​	102​	1​	1​	4​		2​	1​	5​
4​	103​	3​	2​	6​		3​	2​	6​
5​

Formula in D2 copied down
=SUMIFS(H:H,F:F,B2,G:G,C2)

Hope this helps

M.

 

[StephenSLR]

Try something like this

Hope this helps

Thanks for replying.

Here's the issue: designs constantly change and my boss constantly changes his mind about the hours required when the client comes back and says our price is too high. Yes I know, we should just reduce the rate but my boss doesn't think that way, he asks me to change the hours instead.

	A​	B​	C​	D ​	E​	F​	G​	H​
1 ​	Description ​	Bedrooms ​	Bathrooms​	​		​	​	Formula ​
2​	2 beds, 2 baths ​	2​	2 ​	​		​	​	4 ​
3​	1 bed, 1 bath ​	1 ​	1​	​		​	​	5​
4​	3 beds, 2 baths ​	3​	2​	​		​	​	6​
5​	2 beds, 1 bath	2	1					3.5

<tbody>
</tbody>

I will need a table something like above ^ perhaps not with bedroom and bathroom columns; to be used as the reference to source from.

A formula in column H below will match what is in colum B & C and then extract the hours from column H above. As the hours will be ever changing I expect the values in colum H below to change when the values above do.

Then another table with units, etc. as a 'quantities' table.

	A​	B​	C​	D ​	E​	F​	G​	H​
1 ​	Units ​	Bedrooms​	Bathrooms​	Balconies ​		​	​	Formula cells ​
2​	101 ​	2​	1​	1 ​		​	​	4 ​
3​	102 ​	1​	1​	1 ​		​	​	5​
4​	103 ​	3​	2​	2 ​		​	​	6 ​
5​	104	0	1	0				3

<tbody>
</tbody>

From this quantities table ^ the no. of beds, baths, etc. can change.

One minute unit 101 will have 1 bathroom, in the next design revision 101 will have 2 bathrooms.
So when I get that update I will type the '2' into cell C2 and H2 should automatically change to 3.5

Another example would be studio apartments now have balconies, etc.

I hope this is coming across clearer; this is just the way I would expect it to work, perhaps there is an easier method.

s

 

[StephenSLR]

Oops, I have no edit feature, it should read:

One minute unit 101 will have 1 bathroom, in the next design revision 101 will have 2 bathrooms.
So when I get that update I will type the '2' into cell C2 and H2 should automatically change from 4 to 4.5 as a unit with an extra bathroom will take longer to clean.

s

 

[Marcelo Branco]

One minute unit 101 will have 1 bathroom, in the next design revision 101 will have 2 bathrooms.
So when I get that update I will type the '2' into cell C2 and H2 should automatically change to 3.5

It seems to me that my suggestion in post 2 does exactly what you need, that is when you change the number of bedrooms or bathrooms it updates the result automatically. Have you tried it?

Sorry, i don't understand what you are trying to do. Maybe someone else can help.

M.

 

[StephenSLR]

It seems to me that my suggestion in post 2 does exactly what you need, that is when you change the number of bedrooms or bathrooms it updates the result automatically. Have you tried it?

My apologies, I just tried it and I think it's getting close.

For some reason, when I copied the formula down, the result is 5 in every row below in the D column, even when I change values it remains as 5.

It's as if it has taken only your H3 value and copied it down each row.

s

 

[StephenSLR]

I've tried it on a brand new sheet and it works perfectly so something is not quite right on my actual work sheet.

Thank you; I'll try to figure this one out and let you know.

s

 

[Marcelo Branco]

Repeating post 2

Let's say you initially have this

	A​	B ​	C​	D​	E​	F​	G​	H​
1​	Unit​	Bedrooms ​	Bathrooms​	Result​		Bedrooms​	Bathrooms​	Hours​
2​	101​	2​	1​	5​		1​	1​	4​
3​	102​	1​	1​	4​		2​	1​	5​
4​	103​	3 ​	2​	6​		3​	2​	6​
5​

<tbody>
</tbody>

Formula in D2 copied down
=SUMIFS(H:H,F:F,B2,G:G,C2)

Then you change the number of bedrooms of Unit 101 to 1 (B2 in red)

	A​	B ​	C​	D​	E​	F​	G​	H​
1​	Unit​	Bedrooms ​	Bathrooms​	Result​		Bedrooms​	Bathrooms​	Hours​
2​	101​	1 ​	1​	4​		1​	1​	4​
3​	102​	1 ​	1​	4​		2​	1​	5​
4​	103​	3 ​	2​	6​		3​	2​	6​
5​

<tbody>
</tbody>

The result in D2 updates automatically to 4 - bingo!

Try it.

M.

 

[StephenSLR]

It's now working perfectly!

I'm very happy with your solution, it will save me lots of time.

Now to stretch it further; would it be possible to have the greyed out area above my quantities table for easy reference?

The only reason I ask this is because the bedrooms, bathrooms, windows, etc. can stretch across to the Z column and I have to scroll sideways and upward to find my data source.

It's so much easier for me to hit 'Ctrl + Home' to take me to the top of the sheet when I need to do this.

If this is not possible, no worries, your solution has helped me immensely.

Thank you so much.

s

 

[Marcelo Branco]

You are welcome.

About your question
"Now to stretch it further; would it be possible to have the greyed out area above my quantities table for easy reference?"

You must create the reference table including everything: bedrooms, bathrooms, balconies, windows etc and adjust the formula properly.

Example

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Unit​

Bedrooms​

Bathrooms​

Balconies​

Windows​

Result​

Bedrooms​

Bathrooms​

Balconies​

Windows​

Hours​

2​

101​

2​

1​

0​

4​

5​

1​

1​

0​

3​

4​

3​

102​

1​

1​

0​

3​

4​

2​

1​

0​

4​

5​

4​

103​

3​

2​

1​

5​

6​

3​

2​

1​

5​

6​

5​

Formula in F2 copied down
=SUMIFS(L:L,H:H,B2,I:I,C2,J:J,D2,K:K,E2)

M.