not sure how to do a formula

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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?
 
Try it this way:

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

Hope this helps.

Pete
 
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*D1:D25)). I know its not right but was wondering how you would include the 4 codes?
 
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
 
Was thinking more of specific ones to look for.

Pete_UK said:
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
Click to expand...
 
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
 
Back
Top