# nested IFs and ANDs

#### Peter3580

##### New Member
 A K 10 9 8 7 6 2 8 7 1 J 1 K Q 7 3
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <tbody> </tbody>

I haveseven columns of data.
An eighth column shows the total for the row.
I wantto count when A (Ace), K (King) or Q (Queen) occurs in columns 1, 2 and 3 butonly when the eighth column is greater than 3.
A canonly occur in column 1
K canoccur in column 1 and 2
Q canoccur in column 1 2 and 3

Can dothe IF parts but hopless when trying to put the AND part in
Thanks

Peter
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <tbody> </tbody>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Special-K99

##### Well-known Member
So in your top row A is in column 1 and K is in column 2
Should the count be 1 or 2 ?

#### Peter3580

##### New Member
I want the answer, 1, to be in 3 columns if there is A, K and Q in the column and in 4 rows. So that i have a 3x4 matrix filled with 1s. Does this make any sense.

Thanks

Peter

Last edited:

#### Special-K99

##### Well-known Member
Im still a little confused. You're better off producing what you expect the output to look like.

#### Peter3580

##### New Member

<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> </o:lock></vath></v:stroke></v:shapetype>
<tbody> </tbody>
AK10987628LTC=4110
71000
J1000
KQ73110
KQ4 3LTC=6 110
J103 3 000
AKQ4 4 111
Q52 3 100
K10975 5LTC=7 100
Q1032 4 100
4 1 000
Q52 3 100The data depicts a duplicate bridge hand.
13 cards in each hand. I am trying to measure the Losing Trick Count.
The LTC depends on the number of cards in each suit and the amount
of Aces, Kings and Queens held.

Peter
sorry I don't know how to add text. hope this helps
<colgroup><col width="80" style="width: 60pt;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <col width="15" style="width: 11pt; mso-width-source: userset; mso-width-alt: 433;"> <col width="80" style="width: 60pt;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 374;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="3"> <col width="80" style="width: 60pt;" span="7"> <tbody> </tbody>

#### Peter3580

##### New Member
 A K 10 9 8 7 6 2 8 LTC=4 1 1 0 7 1 0 0 0 J 1 0 0 0 K Q 7 3 1 1 0 K Q 4 3 LTC=6 1 1 0 J 10 3 3 0 0 0 A K Q 4 4 1 1 1 Q 5 2 3 1 0 0 K 10 9 7 5 5 LTC=7 1 0 0 Q 10 3 2 4 1 0 0 4 1 0 0 0 Q 5 2 3 1 0 0
<colgroup><col width="80" style="width: 60pt;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <col width="15" style="width: 11pt; mso-width-source: userset; mso-width-alt: 433;"> <col width="80" style="width: 60pt;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 374;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="3"> <col width="80" style="width: 60pt;"> <tbody> </tbody>

#### Peter3580

##### New Member

 ​ A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K ​ LTC​ 1​ SPADES A​ K​ 10​ 9​ 8​ 7​ 6​ 2​ 8 ​ 1​ 1​ 0​ 2​ HEARTS 7​ 1 ​ 0​ 0​ 0​ 3​ CLUBS J​ 1 ​ 0​ 0​ 0​ 4​ DIAMOND K​ Q​ 7​ 3 ​ 0​ 1​ 1
<tbody> </tbody>
<o> </o>
The above table represents a Duplicate Bridge hand of 13playing cards.
<o> </o>
I am trying to write a formula that will calculate theLosing Trick Count (LTC) of the hand.
<o> </o>
Only the first threecards in any suit can be losers - Only the Ace, King, and Queen are winners<o></o>
<o> </o>
In the SPADES suit are 8 cards – the Ace and King arewinners, so there is 1 ‘loser’
<o> </o>
In the HEARTS suit there is 1 card there it is not the Ace thereforeis a ‘loser’. Only 1 card is held in this suit so there can only be 1 ‘loser’.
<o> </o>
In the CLUBS suit there is also only 1 card, which is notthe Ace therefore is a ‘loser.
<o> </o>
In the DIAMONDS suit there are 3 cards – the King and Queenare winners but there is no Ace therefore there is 1 ‘loser’.
<o> </o>
The Losing Trick Count for this hand is 4.
<o> </o>
I want a formula to calculate the LTC along the lines of:
<o> </o>
If B1(throughto B4) = A, K or Q and K1(through to K4) is equal to, or more than 3 then enter 1 in the table
If B1(throughto B4) = K or Q and K8 is equal to, or more than 3 then enter 1 in the table
If B1(throughto B4) = Q and K8 is equal to, or more than 3 then enter 1 in the table

#### Peter3580

##### New Member
nobody can help then, thought it might be a simple task for the experts, but, hey ho. I will try elsewhere. Anyway thanks for previous help

Regards

Peter

Last edited:

#### mikerickson

##### MrExcel MVP
I would use a different system of representing cards, using numbers 13=A 12=K, 11 = Q etc.

Then =SUMIF(A1:C1, "<11") would return the number of losers in row 1.

Or you could use this to count the losers in row 9.
=SUMPRODUCT(--(MATCH(A9:C9,{"A","K","Q","J",10,9,8,7,6,5,4,3,2},0)>3))

Last edited:

#### Peter3580

##### New Member
Thanks. I will give this a try.

Peter

Replies
7
Views
2K
Replies
23
Views
936
Replies
10
Views
1K
Replies
9
Views
194
Replies
6
Views
312

1,148,290
Messages
5,745,879
Members
423,983
Latest member
blackworx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back