| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF - multiple conditions | Excel Worksheet Functions | |||
| multiple conditions in COUNTIF (Excel 2000) | Excel Discussion (Misc queries) | |||
| countif function with multiple criteria | Excel Discussion (Misc queries) | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
| "countif" from multiple worksheets within workbook | Excel Discussion (Misc queries) | |||
Linear Mode
