how to do a double lookup

  • Thread starter Thread starter kc0hwa
  • Start date Start date
K

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
 
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
Click to expand...

Possibly...

=SUM(COUNTIF($A$3:$A30,4),COUNTIF($B$3:$B30,5))
 
kc0hwa said:
how to do a double lookup
countif($a$3:$a30,4) and ($b$3:$b30,5)
Click to expand...

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 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.
Click to expand...

if the cell, is string will this work
 
will this work if the cell have text
like need a like the =countif(sumprotuce($A$3:$A5,$B$3:$B5),$A5&$B5)
 
kc0hwa said:
* 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))
Click to expand...
[....]
if the cell, is string will this work
Click to expand...

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 said:
* 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))
Click to expand...
[....]
if the cell, is string will this work
Click to expand...

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.
Click to expand...

ya THAT IS NOT WORKING

COUNT FROM TOP DOWN FOR X

X=CONCATENATE(A30,D30)
=COUNTIF(CONCATENATE(A2:A30,D2:D30),CONCATENATE(A30,D30))
 
kc0hwa said:
* 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))
Click to expand...
[....]
if the cell, is string will this work
Click to expand...

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.
Click to expand...

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

COUNTIF(($A$2:$A40,$A40) AND (COUNTIF($D$2:$D40,$D40))
 
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
 
Back
Top