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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(CriteriaB2))) ?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

=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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(CriteriaB2))) ?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(CriteriaB2))) ?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(CriteriaB2))) ?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

Hi Karol

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

--
Regards

Roger Govier


"Karol Satka" wrote in message
...
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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(CriteriaB2)))
?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

=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" wrote in message
...
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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe

SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(Crit
eriaB2))) ?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

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" wrote in message
...
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 CriteriaA:(Range A="string*") AND CriteriaB:(Range
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 whe

SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(Crit
eriaB2))) ?

Many thanks in advance for any tips.

COUNTIF multiple crietria
 

Glad to help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Karol Satka" wrote in message
...
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" wrote in message
...
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 CriteriaA:(Range A="string*") AND
CriteriaB:(Range
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 whe


SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(Crit
eriaB2))) ?

Many thanks in advance for any tips.