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

maybe this

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

Mike

On Jan 16, 8:59*pm, John wrote:
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?

"Mike H" wrote:


maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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

Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34*pm, John wrote:
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?



"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -

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?

"Pete_UK" wrote:

Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34 pm, John wrote:
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?



"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -

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

On Jan 17, 8:32*pm, John wrote:
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:D*25)). *I know its not right but was wondering how you would include the 4 codes?



"Pete_UK" wrote:
Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34 pm, John wrote:
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?

"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Was thinking more of specific ones to look for.

"Pete_UK" wrote:

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

On Jan 17, 8:32 pm, John wrote:
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:DÂ*25)). I know its not right but was wondering how you would include the 4 codes?



"Pete_UK" wrote:
Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34 pm, John wrote:
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?

"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

On Jan 17, 11:12*pm, John wrote:
Was thinking more of specific ones to look for.



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

On Jan 17, 8:32 pm, John wrote:
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:D**25)). *I know its not right but was wondering how you would include the 4 codes?

"Pete_UK" wrote:
Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34 pm, John wrote:
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?

"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

Thank you

"Pete_UK" wrote:

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

On Jan 17, 11:12 pm, John wrote:
Was thinking more of specific ones to look for.



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

On Jan 17, 8:32 pm, John wrote:
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:DÂ*Â*25)). I know its not right but was wondering how you would include the 4 codes?

"Pete_UK" wrote:
Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34 pm, John wrote:
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?

"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

For a thorough explanation of the use of SUMPRODUCT, see he

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

Hope this helps.

Pete

On Jan 18, 3:09*am, John wrote:
I guess I'm not completely sure what you mean, could you provide further
detail?

Thank you



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

On Jan 17, 11:12 pm, John wrote:
Was thinking more of specific ones to look for.

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

On Jan 17, 8:32 pm, John wrote:
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:D***25)). *I know its not right but was wondering how you would include the 4 codes?

"Pete_UK" wrote:
Try it this way:

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

Hope this helps.

Pete

On Jan 17, 2:34 pm, John wrote:
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?

"Mike H" wrote:

maybe this

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

Mike

On Jan 16, 8:59 pm, John wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -