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

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

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

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

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 -

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

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 -

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 -

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

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.

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

HELP! Using IF, AND, OR and COUNTIF in one formula
 

Hi,

Try this

=sumproduct((rangeA="TH")*((rangeB="PS")+(rangeB=" RS"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"cydkil" wrote in message
...
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.