COUNTIF multiple crietria

[Guest]

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.

 

[Aladin Akyurek]

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

would count all males which are classified as aliens.

 

[Guest]

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*".

 

[Guest]

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.

 

[Roger Govier]

Hi Karol

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

 

[Bob Phillips]

=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)

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.

SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaAx))AND((CriteriaB1)OR(Crit
eriaB2))) ?

 

[Guest]

Bob thank you very much. It works!

Thx one more time.

 

[Bob Phillips]

Glad to help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)