not sure how to do a formula

[John]

I have a 4 dynamic columns of data that connects to an access database.

Code Counts Description Quantity
pro1 2 1
pro2 1 1
pro3 1 1
pro4 4 1
pro4 1 2
pro5 1 1
pro4 2 Coupon:34 1

What I need to do is evaluate Column A(code) to see if it matches "pro4" if
yes than multipy column b(counts) to column e(quantity) then add up pro4
which in this case would equal 6(without the one that says coupon).

Thanks in advance

 

[Mike H]

maybe this

=SUMPRODUCT((A1:A25="Pro4")*(C1:C25<>"Coupon")*(B1:B25*D125))

Mike

 

[John]

the coupon is being calculated in the formula, isn't there a wildcard that
can be put in to say don't include anything that starts with coupon?

 

[Pete_UK]

Try it this way:

=SUMPRODUCT((A1:A25="Pro4")*(LEFT(C1:C25,6)<>"Coupon")*(B1:B25*D125))

Hope this helps.

Pete

 

[John]

Would there be an easy way to include any of the coupon codes for example
coupon code:34 in one field and say coupon code: 32? I know if you use the
right function with the value of 2 then you will get the 2 digit coupon code
but in this formula is there a way to include one or all, such as
=SUMPRODUCT((A1:A25="Pro4")*(RIGHT(C1:C25,2)="21""32""33""34")*(B1:B25*D125)). I know its not right but was wondering how you would include the 4 codes?

 

[Pete_UK]

Going by your example, you could have:

(LEN(C1:C25)>2)

as the condition if you want to count all the cells with a coupon code
(unless you have other codes in column C).

Hope this helps.

Pete

 

[John]

Was thinking more of specific ones to look for.

Going by your example, you could have:

(LEN(C1:C25)>2)

as the condition if you want to count all the cells with a coupon code
(unless you have other codes in column C).

Hope this helps.

Pete

 

[Pete_UK]

Well, this would include all coupon codes:

")*(LEFT(C1:C25,6)="Coupon")*(

and you could select a single code with RIGHT as you have pointed out.
You can set up OR conditions by means of + in the same way as * is
effectively AND, so you could build up further choices this way.

Hope this helps.

Pete

 

[John]

I guess I'm not completely sure what you mean, could you provide further
detail?

Thank you

 

[Pete_UK]

For a thorough explanation of the use of SUMPRODUCT, see here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete