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

Possibly...

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"kc0hwa" <(E-Mail Removed)> wrote:
> 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.

On Jul 17, 5:35*pm, "joeu2004" <joeu2...@foo.bar> wrote:
> "kc0hwa" <kc0...@gmail.com> wrote:
> > 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.

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" <(E-Mail Removed)> wrote:
> On Jul 17, 5:35 pm, "joeu2004" <joeu2...@foo.bar> wrote:
> > * 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.

On Jul 18, 11:13*am, "joeu2004" <joeu2...@foo.bar> wrote:
> "kc0hwa" <kc0...@gmail.com> wrote:
> > On Jul 17, 5:35 pm, "joeu2004" <joeu2...@foo.bar> wrote:
> > > * 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))

On Jul 18, 11:13*am, "joeu2004" <joeu2...@foo.bar> wrote:
> "kc0hwa" <kc0...@gmail.com> wrote:
> > On Jul 17, 5:35 pm, "joeu2004" <joeu2...@foo.bar> wrote:
> > > * 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))

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