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" wrote:

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" wrote:

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

"ShaneDevenshire" wrote:

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*D2:D10/144)

--
Thanks,
Shane Devenshire


"mike" wrote:

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