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

  • Thread starter Thread starter cydkil
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
 
This should do it (if I understand correctly what the OP wants):

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

Hope this helps.

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

--
__________________________________
HTH

Bob

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

Pete
 
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.
 
Back
Top