Countif problem

[htchandler]

=COUNTIFS(ASSIGNED!B5:D338,"1LT",ASSIGNED!B5:D338,"UATT0")

I'm trying to use the above code to count the number of 1LT who also have UATT0 in the same row.

I know from filtering that there are 6 records that fit this count. Can anyone see my error? I keep just getting the numeral zero as a return.

 

[mvptomlinson]

COUNTIFS, when using multiple criteria, used the "AND" operator. Your formula is checking to see if B5:D338 is 1LT "AND" B5:D338 is UATT0. I'm assuming there is no cell in the range where both values are in one cell.

Will 1LT only appear somewhere in column B, and UATT0 only appear in column D? If so, try:

=COUNTIFS(B5:B338,"1LT",D5:D338,"UATT0")

 

[Marcelo Branco]

Hi,

To count the rows in range B5:D338 that have 1LT and UATTO in any order/position maybe this

=SUMPRODUCT(--((COUNTIF(OFFSET(B5:D338,ROW(B5:D338)-ROW(B5),0,1),"1LT")+COUNTIF(OFFSET(B5:D338,ROW(B5:D338)-ROW(B5),0,1),"UATTO"))=2))

HTH

M.

 

[Marcelo Branco]

The formula of my previous post assumes that there are not, in the same row, two/three instances of 1LT or UATTO, ie
1LT 1LT UATTO
UATTO 1LT UATTO
1LT 1LT x
...

If this is possible use the formula below
=SUMPRODUCT(--(((COUNTIF(OFFSET(B5:D338,ROW(B5:D338)-ROW(B5),0,1),"1lt")>=1)+(COUNTIF(OFFSET(B5:D338,ROW(B5:D338)-ROW(B5),0,1),"uatto")>=1))=2))

M.

 

[Marcelo Branco]

I'm not sure if
UATTO (letter O)
or
UATT0 (zero)

I took the first one (letter O) in my formulas. Correct, please, if its wrong.

M.

 

[Aladin Akyurek]

=COUNTIFS(ASSIGNED!B5:D338,"1LT",ASSIGNED!B5:D338,"UATT0")

I'm trying to use the above code to count the number of 1LT who also have UATT0 in the same row.

I know from filtering that there are 6 records that fit this count. Can anyone see my error? I keep just getting the numeral zero as a return.

Given

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>1LT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>UATT0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>GAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1LT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>JAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>1LT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>RAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>UATT0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>UATT0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1LT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>YAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>1LT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1LT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>UATT0</TD></TR></TBODY></TABLE>

Is there any "unexpected" (illegal) record here? If so, which?

What is the expected count for the legal records?

 

[htchandler]

I was able to figure it our right after I posted this. Instead of selecting an array of rows and colums I had to select just columns. Thank you for all the input.