how to do a double lookup

[kc0hwa]

how to do a double lookup

countif($a$3:$a30,4) and ($b$3:$b30,5)
I know countif is countif(range,==)
but I need look in

 

[GS]

After serious thinking kc0hwa wrote :

how to do a double lookup

countif($a$3:$a30,4) and ($b$3:$b30,5)
I know countif is countif(range,==)
but I need look in

Possibly...

=SUM(COUNTIF($A$3:$A30,4),COUNTIF($B$3:$B30,5))

 

[joeu2004]

how to do a double lookup
countif($a$3:$a30,4) and ($b$3:$b30,5)

If you want to count only the instances when both conditions are met in the
same row, then:

* For XL2007 and later:

COUNTIFS($a$3:$a30,4,$b$3:$b30,5)

* For XL2003 and earlier:

SUMPRODUCT(($a$3:$a30=4)*($b$3:$b30=5))

If you want to count when either condition is met, and you want to avoid
double-accounting when both conditions are met in the same row, then:

SUMPRODUCT(--(($a$3:$a30=4)+($b$3:$b30=5)>0))

The double-negative (--) converts TRUE and FALSE to 1 and 0, which
SUMPRODUCT requires.

Multiplication of conditional expressions acts like AND(). Addition acts
like OR(). We cannot use AND() and OR() in this context for the purposes
described above.

 

[kc0hwa]

If you want to count only the instances when both conditions are met in the
same row, then:

* For XL2007 and later:

  COUNTIFS($a$3:$a30,4,$b$3:$b30,5)

* For XL2003 and earlier:

  SUMPRODUCT(($a$3:$a30=4)*($b$3:$b30=5))

If you want to count when either condition is met, and you want to avoid
double-accounting when both conditions are met in the same row, then:

  SUMPRODUCT(--(($a$3:$a30=4)+($b$3:$b30=5)>0))

The double-negative (--) converts TRUE and FALSE to 1 and 0, which
SUMPRODUCT requires.

Multiplication of conditional expressions acts like AND().  Addition acts
like OR().  We cannot use AND() and OR() in this context for the purposes
described above.

if the cell, is string will this work

 

[kc0hwa]

will this work if the cell have text
like need a like the =countif(sumprotuce($A$3:$A5,$B$3:$B5),$A5&$B5)

 

[joeu2004]

* For XL2007 and later:
COUNTIFS($a$3:$a30,4,$b$3:$b30,5)

* For XL2003 and earlier:
SUMPRODUCT(($a$3:$a30=4)*($b$3:$b30=5)) [....]
SUMPRODUCT(--(($a$3:$a30=4)+($b$3:$b30=5)>0))

[....]
if the cell, is string will this work

The COUNTIFS works regardless of whether the cell content is text or
numeric.

The SUMPRODUCT needs to be changed as follows:

SUMPRODUCT(($a$3:$a30="4")*($b$3:$b30="5"))

or

SUMPRODUCT(--(($a$3:$a30="4")+($b$3:$b30="5")>0))

If you have further questions, please indicate what version of Excel you are
using (XL2003, XL2007, XL2010, etc) and which solution best fits your needs:
COUNTIFS, SUMPRODUCT with "*", or SUMPRODUCT with "+".

That way, I do not have to duplicate so many things.

 

[kc0hwa]

* For XL2007 and later:
COUNTIFS($a$3:$a30,4,$b$3:$b30,5)
* For XL2003 and earlier:
SUMPRODUCT(($a$3:$a30=4)*($b$3:$b30=5)) [....]
SUMPRODUCT(--(($a$3:$a30=4)+($b$3:$b30=5)>0))

[....]
if the cell, is string will this work

The COUNTIFS works regardless of whether the cell content is text or
numeric.

The SUMPRODUCT needs to be changed as follows:

SUMPRODUCT(($a$3:$a30="4")*($b$3:$b30="5"))

or

SUMPRODUCT(--(($a$3:$a30="4")+($b$3:$b30="5")>0))

If you have further questions, please indicate what version of Excel you are
using (XL2003, XL2007, XL2010, etc) and which solution best fits your needs:
COUNTIFS, SUMPRODUCT with "*", or SUMPRODUCT with "+".

That way, I do not have to duplicate so many things.

ya THAT IS NOT WORKING

COUNT FROM TOP DOWN FOR X

X=CONCATENATE(A30,D30)
=COUNTIF(CONCATENATE(A2:A30,D230),CONCATENATE(A30,D30))

 

[kc0hwa]

* For XL2007 and later:
COUNTIFS($a$3:$a30,4,$b$3:$b30,5)
* For XL2003 and earlier:
SUMPRODUCT(($a$3:$a30=4)*($b$3:$b30=5)) [....]
SUMPRODUCT(--(($a$3:$a30=4)+($b$3:$b30=5)>0))

[....]
if the cell, is string will this work

The COUNTIFS works regardless of whether the cell content is text or
numeric.

The SUMPRODUCT needs to be changed as follows:

SUMPRODUCT(($a$3:$a30="4")*($b$3:$b30="5"))

or

SUMPRODUCT(--(($a$3:$a30="4")+($b$3:$b30="5")>0))

If you have further questions, please indicate what version of Excel you are
using (XL2003, XL2007, XL2010, etc) and which solution best fits your needs:
COUNTIFS, SUMPRODUCT with "*", or SUMPRODUCT with "+".

That way, I do not have to duplicate so many things.

ya THAT IS NOT WORKING
COUNT FROM TOP DOWN FOR X
X=CONCATENATE(A30,D30)
=COUNTIF(CONCATENATE(A2:A30,D230),CONCATENATE(A30,D30))
OR

COUNTIF(($A$2:$A40,$A40) AND (COUNTIF($D$2:$D40,$D40))

 

[kc0hwa]

so if there is information is a40 and there is information in d40 I
can not and to count from a2:a40,d2:d40 for the number of time both
hapons