software

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=quot;string*quot;) AND CriteriaBRange
B=quot;string*quot;). I suppose, that above mentioned functions, can't work with
criteria 's expression quot;string*quot;, 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.

=SUMPRODUCT(--($A$2:$A$10=quot;Malequot;),--($B$2:$B$10=quot;Alienquot;))

would count all males which are classified as aliens.

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

Thanks for your reply.

The problem is that in my case my criterias are quot;CON*quot; or quot;GB*quot; ... I am not
sure if Excel can execute SUMPRODUCT when criterias which are not quot;CONquot; but
quot;CON*quot;.quot;Aladin Akyurekquot; wrote:

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

Thanks for your tip,

Unfortunately it does not solve the problem.

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

SUMPRODUCT(--(RangeA=quot;CONPLquot;);--(RangeA=quot;CONINFquot;) 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=quot;CONPLquot;) OR (RangeA=CONINFquot;)) AND
((RangeB=quot;GBquot;) OR (RangeB=quot;GB1quot;))

It's no problem to write SQL statememnt for this, but if not necessary i
would like to use different option.quot;Aladin Akyurekquot; wrote:

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

Hi Karol

Try
=SUMPRODUCT(ISNUMBER(FIND(quot;CONquot;,RangeA))*RangeB)

--
Regards

Roger Govierquot;Karol Satkaquot; gt; wrote in message
...
gt; Thanks for your tip,
gt;
gt; Unfortunately it does not solve the problem.
gt;
gt; My criterias are quot;CON*quot; and quot;GB*quot;. I am not sure if Excel can execute
gt; the
gt; formulas in this case. I can't wite formulas as:
gt;
gt; SUMPRODUCT(--(RangeA=quot;CONPLquot;);--(RangeA=quot;CONINFquot;) ect) because in this
gt; case
gt; (as I understand it) SUMPRODUCT works like AND function and what I
gt; need is
gt; formula to count rows where ((RangeA=quot;CONPLquot;) OR (RangeA=CONINFquot;)) AND
gt; ((RangeB=quot;GBquot;) OR (RangeB=quot;GB1quot;))
gt;
gt; It's no problem to write SQL statememnt for this, but if not necessary
gt; i
gt; would like to use different option.
gt;
gt;
gt; quot;Aladin Akyurekquot; wrote:
gt;
gt;gt; =SUMPRODUCT(--($A$2:$A$10=quot;Malequot;),--($B$2:$B$10=quot;Alienquot;))
gt;gt;
gt;gt; would count all males which are classified as aliens.
gt;gt;
gt;gt; Karol Satka wrote:
gt;gt; gt; Hi all,
gt;gt; gt;
gt;gt; gt; I am trying to solve this problem for quite a long time, but no
gt;gt; gt; idea what's
gt;gt; gt; wrong. My problem si a typical one. I need to use COUNTIF function
gt;gt; gt; with more
gt;gt; gt; than 1 criteria.
gt;gt; gt; I already tried SUM function (as array formula) or SUMPRODUCT, but
gt;gt; gt; result is
gt;gt; gt; 0, which isn't correct.
gt;gt; gt;
gt;gt; gt; My criterias are CriteriaARange A=quot;string*quot;) AND CriteriaBRange
gt;gt; gt; B=quot;string*quot;). I suppose, that above mentioned functions, can't work
gt;gt; gt; with
gt;gt; gt; criteria 's expression quot;string*quot;, but I am not sure about it.
gt;gt; gt;
gt;gt; gt; Have you got any idea how to solve it?
gt;gt; gt;
gt;gt; gt; Is there any possibility to write formulas whe
gt;gt; gt; SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(CriteriaB2)))
gt;gt; gt; ?
gt;gt; gt;
gt;gt; gt; Many thanks in advance for any tips.
gt;gt;
=SUMPRODUCT(--(LEFT($A$2:$A$10,3)=quot;CONquot;),--(LEFT($B$2:$B$10,2)=quot;GBquot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Karol Satkaquot; gt; wrote in message
...
gt; Thanks for your tip,
gt;
gt; Unfortunately it does not solve the problem.
gt;
gt; My criterias are quot;CON*quot; and quot;GB*quot;. I am not sure if Excel can execute the
gt; formulas in this case. I can't wite formulas as:
gt;
gt; SUMPRODUCT(--(RangeA=quot;CONPLquot;);--(RangeA=quot;CONINFquot;) ect) because in this
case
gt; (as I understand it) SUMPRODUCT works like AND function and what I need is
gt; formula to count rows where ((RangeA=quot;CONPLquot;) OR (RangeA=CONINFquot;)) AND
gt; ((RangeB=quot;GBquot;) OR (RangeB=quot;GB1quot;))
gt;
gt; It's no problem to write SQL statememnt for this, but if not necessary i
gt; would like to use different option.
gt;
gt;
gt; quot;Aladin Akyurekquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--($A$2:$A$10=quot;Malequot;),--($B$2:$B$10=quot;Alienquot;))
gt; gt;
gt; gt; would count all males which are classified as aliens.
gt; gt;
gt; gt; Karol Satka wrote:
gt; gt; gt; Hi all,
gt; gt; gt;
gt; gt; gt; I am trying to solve this problem for quite a long time, but no idea
what's
gt; gt; gt; wrong. My problem si a typical one. I need to use COUNTIF function
with more
gt; gt; gt; than 1 criteria.
gt; gt; gt; I already tried SUM function (as array formula) or SUMPRODUCT, but
result is
gt; gt; gt; 0, which isn't correct.
gt; gt; gt;
gt; gt; gt; My criterias are CriteriaARange A=quot;string*quot;) AND CriteriaBRange
gt; gt; gt; B=quot;string*quot;). I suppose, that above mentioned functions, can't work
with
gt; gt; gt; criteria 's expression quot;string*quot;, but I am not sure about it.
gt; gt; gt;
gt; gt; gt; Have you got any idea how to solve it?
gt; gt; gt;
gt; gt; gt; Is there any possibility to write formulas whe
gt; gt; gt;
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(Crit
eriaB2))) ?
gt; gt; gt;
gt; gt; gt; Many thanks in advance for any tips.
gt; gt;
Bob thank you very much. It works!

Thx one more time.

quot;Bob Phillipsquot; wrote:

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

Glad to help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Karol Satkaquot; gt; wrote in message
...
gt; Bob thank you very much. It works!
gt;
gt; Thx one more time.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(LEFT($A$2:$A$10,3)=quot;CONquot;),--(LEFT($B$2:$B$10,2)=quot;GBquot;))
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Karol Satkaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thanks for your tip,
gt; gt; gt;
gt; gt; gt; Unfortunately it does not solve the problem.
gt; gt; gt;
gt; gt; gt; My criterias are quot;CON*quot; and quot;GB*quot;. I am not sure if Excel can execute
the
gt; gt; gt; formulas in this case. I can't wite formulas as:
gt; gt; gt;
gt; gt; gt; SUMPRODUCT(--(RangeA=quot;CONPLquot;);--(RangeA=quot;CONINFquot;) ect) because in this
gt; gt; case
gt; gt; gt; (as I understand it) SUMPRODUCT works like AND function and what I
need is
gt; gt; gt; formula to count rows where ((RangeA=quot;CONPLquot;) OR (RangeA=CONINFquot;)) AND
gt; gt; gt; ((RangeB=quot;GBquot;) OR (RangeB=quot;GB1quot;))
gt; gt; gt;
gt; gt; gt; It's no problem to write SQL statememnt for this, but if not necessary
i
gt; gt; gt; would like to use different option.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Aladin Akyurekquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--($A$2:$A$10=quot;Malequot;),--($B$2:$B$10=quot;Alienquot;))
gt; gt; gt; gt;
gt; gt; gt; gt; would count all males which are classified as aliens.
gt; gt; gt; gt;
gt; gt; gt; gt; Karol Satka wrote:
gt; gt; gt; gt; gt; Hi all,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am trying to solve this problem for quite a long time, but no
idea
gt; gt; what's
gt; gt; gt; gt; gt; wrong. My problem si a typical one. I need to use COUNTIF function
gt; gt; with more
gt; gt; gt; gt; gt; than 1 criteria.
gt; gt; gt; gt; gt; I already tried SUM function (as array formula) or SUMPRODUCT, but
gt; gt; result is
gt; gt; gt; gt; gt; 0, which isn't correct.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; My criterias are CriteriaARange A=quot;string*quot;) AND
CriteriaBRange
gt; gt; gt; gt; gt; B=quot;string*quot;). I suppose, that above mentioned functions, can't
work
gt; gt; with
gt; gt; gt; gt; gt; criteria 's expression quot;string*quot;, but I am not sure about it.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Have you got any idea how to solve it?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there any possibility to write formulas whe
gt; gt; gt; gt; gt;
gt; gt;
SUMPRODUCT(((CriteriaA1)OR(CriteriaA2)OR(CriteriaA x))AND((CriteriaB1)OR(Crit
gt; gt; eriaB2))) ?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Many thanks in advance for any tips.
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;