IF and AND

  • Thread starter Thread starter Tom Quaintance
  • Start date Start date
T

Tom Quaintance

Is it possible to build a logical statement using both IF
and AND. For example, we have an Excel spreadsheet with
Client biling information. Different standard rates apply
to specific Clients and certain premium rates apply for
given activities. I want to be able to look at the Client
name and the Activity and return a billing value for the
spreadsheet. Does anyone know how to do that?
 
You would end up using a lot of IFs. Probably better to use some kind of lookup table, and you
could then use something like SUMPRODUCT to return a value from it depending on the variables,
eg:-

A B C
1 abc qwe 5
2 abc wer 8
3 abc ert 9
4 efg rty 8
5 efg tyu 4
6 ghi yui 5
7 ghi uio 3
8 ghi iop 2
9 ghi asd 1

=SUMPRODUCT((A1:A9="efg)*(B1:B9="tyu")*(C1:C9) will return 4

You can have the values "efg" and "tyu" in cells and reference those too, eg with efg in H1 and
tyu in H2:-

=SUMPRODUCT((A1:A9=H1)*(B1:B9=H2)*(C1:C9) will return 4


Another way is to use the table above with a helper column

A B C
1 abc qwe 5 abcqwe
2 abc wer 8 abcwer
3 abc ert 9 abcert
4 efg rty 8 efgrty
5 efg tyu 4 =A5&B5
6 ghi yui 5 =A6&B6
7 ghi uio 3 etc
8 ghi iop 2
9 ghi asd 1

Assume in cells D1:D9 you had =A1&B1 copied down, and then you could use, assuming the lookup data
in cells H1 and H2 again:-

=INDEX(C1:C9,MATCH(H1&H2,D1:D9,0))

Then just use these formulas inside whatever your own formula would be instead of a hardcoded
number.
 
Back
Top