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

 

[StephenSLR]

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

Yes I get that and that works.

My question is this; can the source data table be at the top?

Ignore the first few columns below, I'm new to this and don't know how to insert a table to this post.

Something like this:

Source data

	​	​	​	​	​	A ​	B ​	C ​
1​	​		​			Bedrooms​	Bathrooms​	Hours​
2​	​	​	​	​		1​	1​	4​
3​	​	​	​	​		2​	1​	5​
4​	​	​	​	​		3​	2​	6​
5​

<tbody>
</tbody>

Quantities

A​

B​

C​

D​

E​

F​

1 ​

Unit​

Bedrooms​

Bathrooms​

Balconies​

Windows​

Result​

2 ​

101​

2​

1​

0​

4​

5 ​

3 ​

102​

1​

1​

0​

3​

4​

​

4​

103​

3​

2​

1​

5​

6​

​

5​

<tbody>
</tbody>

Where the table below references the table above for values.

Is this possible?

s

 

[StephenSLR]

Not to worry, I figured out how to do this by narrowing down the column selection in the table up top to c2:c4 in (the above case) and changing the formula to suit the rows applicable.

Let's say the table below is 10 rows further down the formula would be:

=SUMIFS($C$2:$C$4,$A$2:$A$4,B12,$B$2:$B$4,C12)

Thanks again.

s