| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel converts automaticaly 6digit Product ID into date | Excel Worksheet Functions | |||
| product date stamp file save | Excel Discussion (Misc queries) | |||
| Is this formula 100*(PRODUCT(1+C3:N3/100)-1) recognised by Excel | Excel Worksheet Functions | |||
| Need Help with a Date Sum Product | Excel Worksheet Functions | |||
| A coupon organizer with product name, value, exp. date, etc. | Excel Discussion (Misc queries) | |||
Linear Mode
