if and or statements

  • Thread starter Debbie via OfficeKB.com
  • Start date
D

Debbie via OfficeKB.com

Hi, I have the following statement that works for what I need HOWEVER I now
need to also count the number of times "4.04" u2:u226 contains . I can't
figure out how to add this extra search criteria.

=SUM((COUNTIF(U2:U46,"3.028")+COUNTIF(U62:U226,"3.028"))/COUNTA(U2:U46,U62:
U226))

I have column T2:t460 that contains product 3000, 4000,5000, 6000, 8000.
Then in column U2:u460 I have various versions. I need to figure the
percentage of 3000 & 5000 that equal 3.028 or 4.04 thanks for any help you
can offer.
 
B

Bob Phillips

=(SUMPRODUCT(COUNTIF(U2:U46,{"3.028","4.04"}))+
SUMPRODUCT(COUNTIF(U62:U226,{"3.028","4.04"})))/COUNTA(U2:U46,U62:U226)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
D

Debbie via OfficeKB.com

Bob - Thank you soooo much. This works fabulously! I just have one more
question if you have time to answer. What do the { } brackets indicate as
opposed to ( )?
 
B

Bob Phillips

Hi Debbie,

The curly brackets are used to contain an array of constant values, so the
COUNTIF works on the array, that is both 3.028 and 4.04, rather than a
single value

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
D

Debbie via OfficeKB.com

Bob, Excellent, I can't thank you enough for this information!!!

Bob said:
Hi Debbie,

The curly brackets are used to contain an array of constant values, so the
COUNTIF works on the array, that is both 3.028 and 4.04, rather than a
single value

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Bob - Thank you soooo much. This works fabulously! I just have one more
question if you have time to answer. What do the { } brackets indicate as
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Top