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

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

​

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

​

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

​

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

​

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

​

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

A
​

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

B
​

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

C
​

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

1​

[/TD]
[TD]

​

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

​

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

Bedrooms​

[/TD]
[TD]

Bathrooms​

[/TD]
[TD]

Hours​

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

2​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/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]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/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]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/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]



Quantities

[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"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/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"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/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"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

​

[/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]
</tbody>[/TABLE]


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