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

[cydkil]

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

 

[Pete_UK]

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

 

[Mike H]

Hi,

Your a bit thin on detail here but maybe something along these lines

=IF(AND(A1=1,OR(B1=1,C1=1)),COUNTIF(D120,"A"),"")

Mike

 

[David Biddulph]

Wouldn't that count double if B and C were both true?
--
David Biddulph

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

 

[Pete_UK]

Yes, you're right, David - back to the drawing board !!

Pete

 

[cydkil]

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

 

[Pete_UK]

This should do it (if I understand correctly what the OP wants):

=SUMPRODUCT((A1:A100)*(B1:B100+C1:C100>0))

Hope this helps.

Pete

 

[Bob Phillips]

=SUMPRODUCT((A1:A100)*(SIGN((B1:B100)+(C1:C100))))

--
__________________________________
HTH

Bob

Yes, you're right, David - back to the drawing board !!

Pete

 

[Bob Phillips]

=SUMPRODUCT(('2009'!O5:O18={"PS","RS"})*('2009'!J5:J18="TH"))

 

[cydkil]

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.

 

[cydkil]

WooHoo! We have a winner!!!

thanks for all the help everyone - cydkil

 

[Ashish Mathur]

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com