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)

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Unit​

[/td][td]

Bedrooms​

[/td][td]

Bathrooms​

[/td][td]

Result​

[/td][td][/td][td]

Bedrooms​

[/td][td]

Bathrooms​

[/td][td]

Hours​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

101​

[/td][td]

2​

[/td][td]

1​

[/td][td]

5​

[/td][td][/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

102​

[/td][td]

1​

[/td][td]

1​

[/td][td]

4​

[/td][td][/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

103​

[/td][td]

3​

[/td][td]

2​

[/td][td]

6​

[/td][td][/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.


[TABLE="class: grid"]
<tbody>[TR="class: px"wysiwyg_dashes"]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D
​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1
​

[/TD]
[TD]

Description
​

[/TD]
[TD]

Bedrooms
​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

Formula
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

2 beds, 2 baths
​

[/TD]
[TD]

2​

[/TD]
[TD]

2
​

[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

4
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1 bed, 1 bath
​

[/TD]
[TD]

1
​

[/TD]
[TD]

1​

[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

3 beds, 2 baths
​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]2 beds, 1 bath
[/TD]
[TD] 2
[/TD]
[TD] 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 3.5
[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D
​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1
​

[/TD]
[TD]

Units
​

[/TD]
[TD]

Bedrooms​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

Balconies
​

[/TD]
[TD][/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

Formula cells
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101
​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

1
​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

4
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

102
​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1
​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

103
​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2
​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

​

[/TD]
[TD="bgcolor: #D9D9D9"]

6
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD] 104
[/TD]
[TD] 0
[/TD]
[TD] 1
[/TD]
[TD] 0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 3
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B
​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Unit​

[/TD]
[TD]

Bedrooms
​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

Bedrooms​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

Hours​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

102​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

103​

[/TD]
[TD]

3
​

[/TD]
[TD]

2​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B
​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Unit​

[/TD]
[TD]

Bedrooms
​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

Bedrooms​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

Hours​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101​

[/TD]
[TD]

1
​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

102​

[/TD]
[TD]

1
​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

103​

[/TD]
[TD]

3
​

[/TD]
[TD]

2​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Unit​

[/td][td]

Bedrooms​

[/td][td]

Bathrooms​

[/td][td]

Balconies​

[/td][td]

Windows​

[/td][td]

Result​

[/td][td][/td][td]

Bedrooms​

[/td][td]

Bathrooms​

[/td][td]

Balconies​

[/td][td]

Windows​

[/td][td]

Hours​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

101​

[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td]

4​

[/td][td]

5​

[/td][td][/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

0​

[/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

102​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

3​

[/td][td]

4​

[/td][td][/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

0​

[/td][td="bgcolor:#D9D9D9"]

4​

[/td][td="bgcolor:#D9D9D9"]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

103​

[/td][td]

3​

[/td][td]

2​

[/td][td]

1​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

5​

[/td][td="bgcolor:#D9D9D9"]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

M.