trying to set conditions for a range of cells

[mike]

I'm trying to set up a formula that will do an Equation if a word and a
thickness of lumber is present in any of a range of cells. I have used cells
A2:A10 to be a word, and B2:B10 to be a thickness of lumber. The formula I
have now is =IF(AND(A2="poplar",B2<1),1*C2*D2/144+0.05*C2*D2/144,"") this
works fine but I would like if ANY cells from A2 thru A10 said poplar and ANY
cells from B2 thru B10 is less than 1 it would preform the equation I have.
Right now I have to have multiple columns with the formulas entered for each
type of wood and thickness.
Thanks,
Mike

 

[~L]

You could use Countifs.

=IF(AND(COUNTIF(A2:A10,"poplar")>0,COUNTIF(B2:B10,"<1")>0),1*C2*D2/144+0.05*C2*D2/144,"")

And for better control replace "poplar" and the 1 in "<1" with cell
references.

 

[mike]

Thanks, I entered the formula and it would work for A2 and B2 but when I
enter the parameters in A5 and B5 also it wouldn't see them.

 

[ShaneDevenshire]

Hi,

First you can simplify the calculation in the second half of the formula as
I do below. Second, this question is not clear, let me explain, suppose your
data looks like this:
poplar .5
poplar 6
pine .3
pine 2.9

which lines should you be calculating? The first line meets both
conditions, the second line meets the first but not the second condtion, the
third line meet the second but not the first. And the fourth line meets
neither.

As stated you want to use lines 1, 2, and 3 in the calculation, but my
suspicions are that is not really what you want. I suspect only the first
line fits the condtions.

Here is a formula which I think does what you probably want:

=SUMPRODUCT(--(A2:A10="popular"),--(B2:B10<1),1.05*C2:C10*D210/144)

 

[~L]

If this is going to travel, make the references absolute.

=IF(AND(COUNTIF($A$2:$A$10,"poplar")>0,COUNTIF($B$2:$B$10,"<1")>0),1*C2*D2/144+0.05*C2*D2/144,"")

 

[mike]

Shane,
You are right only line 1 meets both conditions I tried to enter your
formula but it only returns 0.00. I have several of the A and B cells that
meet both conditions so I should be receiving a total of the those cells I
would think. Any help?
Mike

 

[~L]

One thing I can see is, in the formula he has "popular" but in the data
"poplar". Text mismatch would also occur if the data has trailing or leading
spaces. This will cause your formula to produce 0 matches and so have a 0
total.

I'd encourage you to replace the match conditions with cell references to
avoid editing the formula to change your match criteria.

 

[mike]

L,
Thanks I will try the formula tomorrow I hadn't noticed the popular
reference. Could you expand on using a cell reference instead of the text I'm
using. thanks so much for all your help.
Mike

 

[~L]

Sure,

In a cell somewhere out of your data range (in the examples we've been using
so far, row 12 or colum F would work) put Poplar in a cell by itself and the
number 1 in a cell by itself.

Then change the formula to (putting the lookup values in columns F and G
with column headers):

=SUMPRODUCT(--($A$2:$A$10=F2),--($B$2:$B$10<G2),1.05*C2:C10*D210/144)

Then, when you need to evaluate by different criteria, either add to that
list (populating F3 and G3, F4 and G4, etc) or change the values in F2 and G2
to your new criteria. Unless you're changing from a 'less than X inches'
critera to a 'Greater than X inches' Criteria, you won't have to edit the
formula.