Excel Date/Product formula. Please help
 

Good Morning

I was wondering if anyone could give me advice on a formula for picking out
information in a running spreadsheet. I need to get information from a set
month (i.e. october), a specific product and the value of this.

Below is an example of what im working with...

A=Date
B=Product
C=Value

A B C
1 23/10/2007 QB £1000
2 14/11/2007 QB £2300
3 02/10/2007 QB £500.50
4 24/09/2007 S&L £1300
5 15/10/2007 QL £3420
6 18/11/2007 QB £2000

As mentioned above im trying to make a formula which will pick out
information in october for a product called 'QB' and give me a total sum (in
this case £1500.50).

I would greatly appreciate anyones help!

Thank You!

--
Stuart Henry
Stage Systems

Excel Date/Product formula. Please help
 

Try this:

=SUMPRODUCT((MONTH(A1:A100)=10)*(B1:B100="QB")*(C1 :C100))

Adjust the ranges to suit.

Hope this helps.

Pete

On Nov 12, 8:55 am, Mantis2k
wrote:
Good Morning

I was wondering if anyone could give me advice on a formula for picking out
information in a running spreadsheet. I need to get information from a set
month (i.e. october), a specific product and the value of this.

Below is an example of what im working with...

A=Date
B=Product
C=Value

A B C
1 23/10/2007 QB £1000
2 14/11/2007 QB £2300
3 02/10/2007 QB £500.50
4 24/09/2007 S&L £1300
5 15/10/2007 QL £3420
6 18/11/2007 QB £2000

As mentioned above im trying to make a formula which will pick out
information in october for a product called 'QB' and give me a total sum (in
this case £1500.50).

I would greatly appreciate anyones help!

Thank You!

--
Stuart Henry
Stage Systems

Excel Date/Product formula. Please help
 

"Mantis2k" wrote in message
...
Good Morning

I was wondering if anyone could give me advice on a formula for picking
out
information in a running spreadsheet. I need to get information from a set
month (i.e. october), a specific product and the value of this.

Below is an example of what im working with...

A=Date
B=Product
C=Value

A B C
1 23/10/2007 QB £1000
2 14/11/2007 QB £2300
3 02/10/2007 QB £500.50
4 24/09/2007 S&L £1300
5 15/10/2007 QL £3420
6 18/11/2007 QB £2000

As mentioned above im trying to make a formula which will pick out
information in october for a product called 'QB' and give me a total sum
(in
this case £1500.50).

I would greatly appreciate anyones help!

Thank You!

--
Stuart Henry
Stage Systems

=SUMPRODUCT((MONTH(A1:A6)=10)*(B1:B6="QB")*(C1:C6) )

If your dates span more than one year, you may need an additional criterion
(YEAR(A1:A6)=2007)

Excel Date/Product formula. Please help SOLVED
 

Many Thanks Pete_UK and Stephen! Your advice worked a treat!
--
Stuart Henry
Stage Systems


"Mantis2k" wrote:

Good Morning

I was wondering if anyone could give me advice on a formula for picking out
information in a running spreadsheet. I need to get information from a set
month (i.e. october), a specific product and the value of this.

Below is an example of what im working with...

A=Date
B=Product
C=Value

A B C
1 23/10/2007 QB £1000
2 14/11/2007 QB £2300
3 02/10/2007 QB £500.50
4 24/09/2007 S&L £1300
5 15/10/2007 QL £3420
6 18/11/2007 QB £2000

As mentioned above im trying to make a formula which will pick out
information in october for a product called 'QB' and give me a total sum (in
this case £1500.50).

I would greatly appreciate anyones help!

Thank You!

--
Stuart Henry
Stage Systems

Excel Date/Product formula. Please help SOLVED
 

You're welcome - thanks for feeding back.

Pete

On Nov 12, 9:39 am, Mantis2k
wrote:
Many Thanks Pete_UK and Stephen! Your advice worked a treat!
--
Stuart Henry
Stage Systems



"Mantis2k" wrote:
Good Morning

I was wondering if anyone could give me advice on a formula for picking out
information in a running spreadsheet. I need to get information from a set
month (i.e. october), a specific product and the value of this.

Below is an example of what im working with...

A=Date
B=Product
C=Value

A B C
1 23/10/2007 QB £1000
2 14/11/2007 QB £2300
3 02/10/2007 QB £500.50
4 24/09/2007 S&L £1300
5 15/10/2007 QL £3420
6 18/11/2007 QB £2000

As mentioned above im trying to make a formula which will pick out
information in october for a product called 'QB' and give me a total sum (in
this case £1500.50).

I would greatly appreciate anyones help!

Thank You!

--
Stuart Henry
Stage Systems- Hide quoted text -

- Show quoted text -