COUNTIF multiple crietria

Hi all,

I am trying to solve this problem for quite a long time, but no idea what's
wrong. My problem si a typical one. I need to use COUNTIF function with more
than 1 criteria.
I already tried SUM function (as array formula) or SUMPRODUCT, but result is
0, which isn't correct.

My criterias are CriteriaARange A="string*") AND CriteriaBRange
B="string*"). I suppose, that above mentioned functions, can't work with
criteria 's expression "string*", but I am not sure about it.

Have you got any idea how to solve it?

Is there any possibility to write formulas where:
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(CriteriaB2))) ?

Many thanks in advance for any tips.

=SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))

would count all males which are classified as aliens.

Karol Satka wrote:
> Hi all,
>
> I am trying to solve this problem for quite a long time, but no idea what's
> wrong. My problem si a typical one. I need to use COUNTIF function with more
> than 1 criteria.
> I already tried SUM function (as array formula) or SUMPRODUCT, but result is
> 0, which isn't correct.
>
> My criterias are CriteriaARange A="string*") AND CriteriaBRange
> B="string*"). I suppose, that above mentioned functions, can't work with
> criteria 's expression "string*", but I am not sure about it.
>
> Have you got any idea how to solve it?
>
> Is there any possibility to write formulas where:
> SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(CriteriaB2))) ?
>
> Many thanks in advance for any tips.

Thanks for your reply.

The problem is that in my case my criterias are "CON*" or "GB*" ... I am not
sure if Excel can execute SUMPRODUCT when criterias which are not "CON" but
"CON*".


"Aladin Akyurek" wrote:

> =SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))
>
> would count all males which are classified as aliens.
>
> Karol Satka wrote:
> > Hi all,
> >
> > I am trying to solve this problem for quite a long time, but no idea what's
> > wrong. My problem si a typical one. I need to use COUNTIF function with more
> > than 1 criteria.
> > I already tried SUM function (as array formula) or SUMPRODUCT, but result is
> > 0, which isn't correct.
> >
> > My criterias are CriteriaARange A="string*") AND CriteriaBRange
> > B="string*"). I suppose, that above mentioned functions, can't work with
> > criteria 's expression "string*", but I am not sure about it.
> >
> > Have you got any idea how to solve it?
> >
> > Is there any possibility to write formulas where:
> > SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(CriteriaB2))) ?
> >
> > Many thanks in advance for any tips.
>

Thanks for your tip,

Unfortunately it does not solve the problem.

My criterias are "CON*" and "GB*". I am not sure if Excel can execute the
formulas in this case. I can't wite formulas as:

SUMPRODUCT(--(RangeA="CONPL");--(RangeA="CONINF") ect) because in this case
(as I understand it) SUMPRODUCT works like AND function and what I need is
formula to count rows where ((RangeA="CONPL") OR (RangeA=CONINF")) AND
((RangeB="GB") OR (RangeB="GB1"))

It's no problem to write SQL statememnt for this, but if not necessary i
would like to use different option.


"Aladin Akyurek" wrote:

> =SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))
>
> would count all males which are classified as aliens.
>
> Karol Satka wrote:
> > Hi all,
> >
> > I am trying to solve this problem for quite a long time, but no idea what's
> > wrong. My problem si a typical one. I need to use COUNTIF function with more
> > than 1 criteria.
> > I already tried SUM function (as array formula) or SUMPRODUCT, but result is
> > 0, which isn't correct.
> >
> > My criterias are CriteriaARange A="string*") AND CriteriaBRange
> > B="string*"). I suppose, that above mentioned functions, can't work with
> > criteria 's expression "string*", but I am not sure about it.
> >
> > Have you got any idea how to solve it?
> >
> > Is there any possibility to write formulas where:
> > SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(CriteriaB2))) ?
> >
> > Many thanks in advance for any tips.
>

Hi Karol

Try
=SUMPRODUCT(ISNUMBER(FIND("CON",RangeA))*RangeB)

--
Regards

Roger Govier


"Karol Satka" <KarolSatka@discussions.microsoft.com> wrote in message
news:B5FE8B24-5F8F-42B4-8CB3-ECB592BA79B6@microsoft.com...
> Thanks for your tip,
>
> Unfortunately it does not solve the problem.
>
> My criterias are "CON*" and "GB*". I am not sure if Excel can execute
> the
> formulas in this case. I can't wite formulas as:
>
> SUMPRODUCT(--(RangeA="CONPL");--(RangeA="CONINF") ect) because in this
> case
> (as I understand it) SUMPRODUCT works like AND function and what I
> need is
> formula to count rows where ((RangeA="CONPL") OR (RangeA=CONINF")) AND
> ((RangeB="GB") OR (RangeB="GB1"))
>
> It's no problem to write SQL statememnt for this, but if not necessary
> i
> would like to use different option.
>
>
> "Aladin Akyurek" wrote:
>
>> =SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))
>>
>> would count all males which are classified as aliens.
>>
>> Karol Satka wrote:
>> > Hi all,
>> >
>> > I am trying to solve this problem for quite a long time, but no
>> > idea what's
>> > wrong. My problem si a typical one. I need to use COUNTIF function
>> > with more
>> > than 1 criteria.
>> > I already tried SUM function (as array formula) or SUMPRODUCT, but
>> > result is
>> > 0, which isn't correct.
>> >
>> > My criterias are CriteriaARange A="string*") AND CriteriaBRange
>> > B="string*"). I suppose, that above mentioned functions, can't work
>> > with
>> > criteria 's expression "string*", but I am not sure about it.
>> >
>> > Have you got any idea how to solve it?
>> >
>> > Is there any possibility to write formulas where:
>> > SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(CriteriaB2)))
>> > ?
>> >
>> > Many thanks in advance for any tips.
>>

=SUMPRODUCT(--(LEFT($A$2:$A$10,3)="CON"),--(LEFT($B$2:$B$10,2)="GB"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Karol Satka" <KarolSatka@discussions.microsoft.com> wrote in message
news:B5FE8B24-5F8F-42B4-8CB3-ECB592BA79B6@microsoft.com...
> Thanks for your tip,
>
> Unfortunately it does not solve the problem.
>
> My criterias are "CON*" and "GB*". I am not sure if Excel can execute the
> formulas in this case. I can't wite formulas as:
>
> SUMPRODUCT(--(RangeA="CONPL");--(RangeA="CONINF") ect) because in this
case
> (as I understand it) SUMPRODUCT works like AND function and what I need is
> formula to count rows where ((RangeA="CONPL") OR (RangeA=CONINF")) AND
> ((RangeB="GB") OR (RangeB="GB1"))
>
> It's no problem to write SQL statememnt for this, but if not necessary i
> would like to use different option.
>
>
> "Aladin Akyurek" wrote:
>
> > =SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))
> >
> > would count all males which are classified as aliens.
> >
> > Karol Satka wrote:
> > > Hi all,
> > >
> > > I am trying to solve this problem for quite a long time, but no idea
what's
> > > wrong. My problem si a typical one. I need to use COUNTIF function
with more
> > > than 1 criteria.
> > > I already tried SUM function (as array formula) or SUMPRODUCT, but
result is
> > > 0, which isn't correct.
> > >
> > > My criterias are CriteriaARange A="string*") AND CriteriaBRange
> > > B="string*"). I suppose, that above mentioned functions, can't work
with
> > > criteria 's expression "string*", but I am not sure about it.
> > >
> > > Have you got any idea how to solve it?
> > >
> > > Is there any possibility to write formulas where:
> > >
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(Crit
eriaB2))) ?
> > >
> > > Many thanks in advance for any tips.
> >

Bob thank you very much. It works!

Thx one more time.

"Bob Phillips" wrote:

> =SUMPRODUCT(--(LEFT($A$2:$A$10,3)="CON"),--(LEFT($B$2:$B$10,2)="GB"))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Karol Satka" <KarolSatka@discussions.microsoft.com> wrote in message
> news:B5FE8B24-5F8F-42B4-8CB3-ECB592BA79B6@microsoft.com...
> > Thanks for your tip,
> >
> > Unfortunately it does not solve the problem.
> >
> > My criterias are "CON*" and "GB*". I am not sure if Excel can execute the
> > formulas in this case. I can't wite formulas as:
> >
> > SUMPRODUCT(--(RangeA="CONPL");--(RangeA="CONINF") ect) because in this
> case
> > (as I understand it) SUMPRODUCT works like AND function and what I need is
> > formula to count rows where ((RangeA="CONPL") OR (RangeA=CONINF")) AND
> > ((RangeB="GB") OR (RangeB="GB1"))
> >
> > It's no problem to write SQL statememnt for this, but if not necessary i
> > would like to use different option.
> >
> >
> > "Aladin Akyurek" wrote:
> >
> > > =SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))
> > >
> > > would count all males which are classified as aliens.
> > >
> > > Karol Satka wrote:
> > > > Hi all,
> > > >
> > > > I am trying to solve this problem for quite a long time, but no idea
> what's
> > > > wrong. My problem si a typical one. I need to use COUNTIF function
> with more
> > > > than 1 criteria.
> > > > I already tried SUM function (as array formula) or SUMPRODUCT, but
> result is
> > > > 0, which isn't correct.
> > > >
> > > > My criterias are CriteriaARange A="string*") AND CriteriaBRange
> > > > B="string*"). I suppose, that above mentioned functions, can't work
> with
> > > > criteria 's expression "string*", but I am not sure about it.
> > > >
> > > > Have you got any idea how to solve it?
> > > >
> > > > Is there any possibility to write formulas where:
> > > >
> SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(Crit
> eriaB2))) ?
> > > >
> > > > Many thanks in advance for any tips.
> > >
>
>
>

Glad to help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Karol Satka" <KarolSatka@discussions.microsoft.com> wrote in message
news:CF7A7A14-6987-4C79-854E-711E895E9A00@microsoft.com...
> Bob thank you very much. It works!
>
> Thx one more time.
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(LEFT($A$2:$A$10,3)="CON"),--(LEFT($B$2:$B$10,2)="GB"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Karol Satka" <KarolSatka@discussions.microsoft.com> wrote in message
> > news:B5FE8B24-5F8F-42B4-8CB3-ECB592BA79B6@microsoft.com...
> > > Thanks for your tip,
> > >
> > > Unfortunately it does not solve the problem.
> > >
> > > My criterias are "CON*" and "GB*". I am not sure if Excel can execute
the
> > > formulas in this case. I can't wite formulas as:
> > >
> > > SUMPRODUCT(--(RangeA="CONPL");--(RangeA="CONINF") ect) because in this
> > case
> > > (as I understand it) SUMPRODUCT works like AND function and what I
need is
> > > formula to count rows where ((RangeA="CONPL") OR (RangeA=CONINF")) AND
> > > ((RangeB="GB") OR (RangeB="GB1"))
> > >
> > > It's no problem to write SQL statememnt for this, but if not necessary
i
> > > would like to use different option.
> > >
> > >
> > > "Aladin Akyurek" wrote:
> > >
> > > > =SUMPRODUCT(--($A$2:$A$10="Male"),--($B$2:$B$10="Alien"))
> > > >
> > > > would count all males which are classified as aliens.
> > > >
> > > > Karol Satka wrote:
> > > > > Hi all,
> > > > >
> > > > > I am trying to solve this problem for quite a long time, but no
idea
> > what's
> > > > > wrong. My problem si a typical one. I need to use COUNTIF function
> > with more
> > > > > than 1 criteria.
> > > > > I already tried SUM function (as array formula) or SUMPRODUCT, but
> > result is
> > > > > 0, which isn't correct.
> > > > >
> > > > > My criterias are CriteriaARange A="string*") AND
CriteriaBRange
> > > > > B="string*"). I suppose, that above mentioned functions, can't
work
> > with
> > > > > criteria 's expression "string*", but I am not sure about it.
> > > > >
> > > > > Have you got any idea how to solve it?
> > > > >
> > > > > Is there any possibility to write formulas where:
> > > > >
> >
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(Crit
> > eriaB2))) ?
> > > > >
> > > > > Many thanks in advance for any tips.
> > > >
> >
> >
> >