| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP! Using IF, AND, OR and COUNTIF in one formula
I have not been able to get this to display results.
Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
Suppose your range covers rows 1 to 100. Then you would have something
like this: =SUMPRODUCT((A1:A100)*((B1:B100)+(C1:C100))) * is equivalent to AND, + is equivalent to OR. The assumes the cells contain TRUE or FALSE. Hope this helps. Pete On Jan 29, 12:50*pm, cydkil wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
Wouldn't that count double if B and C were both true?
-- David Biddulph "Pete_UK" wrote in message ... Suppose your range covers rows 1 to 100. Then you would have something like this: =SUMPRODUCT((A1:A100)*((B1:B100)+(C1:C100))) * is equivalent to AND, + is equivalent to OR. The assumes the cells contain TRUE or FALSE. Hope this helps. Pete On Jan 29, 12:50 pm, cydkil wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
Yes, you're right, David - back to the drawing board !!
Pete On Jan 29, 2:18*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wouldn't that count double if B and C were both true? -- David Biddulph "Pete_UK" wrote in message ... Suppose your range covers rows 1 to 100. Then you would have something like this: =SUMPRODUCT((A1:A100)*((B1:B100)+(C1:C100))) * is equivalent to AND, + is equivalent to OR. The assumes the cells contain TRUE or FALSE. Hope this helps. Pete On Jan 29, 12:50 pm, cydkil wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
This should do it (if I understand correctly what the OP wants):
=SUMPRODUCT((A1:A100)*(B1:B100+C1:C1000)) Hope this helps. Pete On Jan 29, 3:04*pm, Pete_UK wrote: Yes, you're right, David - back to the drawing board !! Pete On Jan 29, 2:18*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wouldn't that count double if B and C were both true? -- David Biddulph "Pete_UK" wrote in message ... Suppose your range covers rows 1 to 100. Then you would have something like this: =SUMPRODUCT((A1:A100)*((B1:B100)+(C1:C100))) * is equivalent to AND, + is equivalent to OR. The assumes the cells contain TRUE or FALSE. Hope this helps. Pete On Jan 29, 12:50 pm, cydkil wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
=SUMPRODUCT((A1:A100)*(SIGN((B1:B100)+(C1:C100))))
-- __________________________________ HTH Bob "Pete_UK" wrote in message ... Yes, you're right, David - back to the drawing board !! Pete On Jan 29, 2:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wouldn't that count double if B and C were both true? -- David Biddulph "Pete_UK" wrote in message ... Suppose your range covers rows 1 to 100. Then you would have something like this: =SUMPRODUCT((A1:A100)*((B1:B100)+(C1:C100))) * is equivalent to AND, + is equivalent to OR. The assumes the cells contain TRUE or FALSE. Hope this helps. Pete On Jan 29, 12:50 pm, cydkil wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
I will try to verbally explain what I am looking for.
Count the number of times salesperson 'TH' sells product 'PS' or 'RS'. The spreadsheet contains many columns of data including one that displays the sales persons initials as well as another that codes the type of product being sold. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
Hi,
Your a bit thin on detail here but maybe something along these lines =IF(AND(A1=1,OR(B1=1,C1=1)),COUNTIF(D1:D20,"A"),"" ) Mike "cydkil" wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
here is my formula:
=IF(AND('2009'!J5:J18="TH",OR('2009'!O5:O18="PS",' 2009'!O5:O18="RS")),COUNTIF('2009'!J5:J18,"TH"),"" ) result displayed: #VALUE! cyd "Mike H" wrote: Hi, Your a bit thin on detail here but maybe something along these lines =IF(AND(A1=1,OR(B1=1,C1=1)),COUNTIF(D1:D20,"A"),"" ) Mike "cydkil" wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
=SUMPRODUCT(('2009'!O5:O18={"PS","RS"})*('2009'!J5 :J18="TH"))
-- __________________________________ HTH Bob "cydkil" wrote in message ... here is my formula: =IF(AND('2009'!J5:J18="TH",OR('2009'!O5:O18="PS",' 2009'!O5:O18="RS")),COUNTIF('2009'!J5:J18,"TH"),"" ) result displayed: #VALUE! cyd "Mike H" wrote: Hi, Your a bit thin on detail here but maybe something along these lines =IF(AND(A1=1,OR(B1=1,C1=1)),COUNTIF(D1:D20,"A"),"" ) Mike "cydkil" wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! Using IF, AND, OR and COUNTIF in one formula
WooHoo! We have a winner!!!
thanks for all the help everyone - cydkil "Bob Phillips" wrote: =SUMPRODUCT(('2009'!O5:O18={"PS","RS"})*('2009'!J5 :J18="TH")) -- __________________________________ HTH Bob "cydkil" wrote in message ... here is my formula: =IF(AND('2009'!J5:J18="TH",OR('2009'!O5:O18="PS",' 2009'!O5:O18="RS")),COUNTIF('2009'!J5:J18,"TH"),"" ) result displayed: #VALUE! cyd "Mike H" wrote: Hi, Your a bit thin on detail here but maybe something along these lines =IF(AND(A1=1,OR(B1=1,C1=1)),COUNTIF(D1:D20,"A"),"" ) Mike "cydkil" wrote: I have not been able to get this to display results. Here is what I am trying to calculate: IF (A=True) AND (B=True or C=True) then count the occurences of A within a range |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif formula | Excel Discussion (Misc queries) | |||
| Countif formula | Excel Discussion (Misc queries) | |||
| CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
| countif formula | Excel Discussion (Misc queries) | |||
| Countif formula and then some... | Excel Worksheet Functions | |||
Hybrid Mode
