Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
Hi,
Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
=SumProduct(--(A1:A100 = datevalue("4-1-08")),--(A1:A100 <=
datevalue("6-30-08")),--(B1:B100="Pen"),C1:C100) "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
Hi Joel,
Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
The problem is with your data in the cells and not the formula. Make sure
you change the ranges of the data to match you worksheet. The best way of debugging a formula proble is use the evaluate formula tool Menu Tools - formula auditing - evaluate formula As you evaluate the formula you should see which item causes the #value to appear. You may have to run the tool a few times until you see where the problem is located. "Shiva" wrote: Hi Joel, Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
Joel,
Thanks, It's working but shows me wrong sum value which is there in the last row Date Product Cost 02-Mar-08 pen 1.00 03-Apr-08 pencil 2.00 04-Jun-08 Eraser 3.00 05-Jul-08 pen 4.00 06-Sep-08 pencil 5.00 07-May-08 Eraser 5.00 08-Apr-08 pen 1.00 I've modified the formula as below =SUMPRODUCT(--(A2:A8=DATEVALUE("01-04-2008")),--(A2:A8<= DATEVALUE("30-06-2008")),--(B2:B8="Pen"),C2:C8) Can please support me to find number of occurences of each product along with sum of the cost "Joel" wrote: The problem is with your data in the cells and not the formula. Make sure you change the ranges of the data to match you worksheet. The best way of debugging a formula proble is use the evaluate formula tool Menu Tools - formula auditing - evaluate formula As you evaluate the formula you should see which item causes the #value to appear. You may have to run the tool a few times until you see where the problem is located. "Shiva" wrote: Hi Joel, Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
I'm getting the right answer. Why don't you change the date format like I
did below. Maybe there is a problem with having that day first and then the month. =SUMPRODUCT(--(A2:A8=DATEVALUE("01-Apr-08")),--(A2:A8<= DATEVALUE("30-Jun-08")),--(B2:B8="Pen"),C2:C8) "Shiva" wrote: Joel, Thanks, It's working but shows me wrong sum value which is there in the last row Date Product Cost 02-Mar-08 pen 1.00 03-Apr-08 pencil 2.00 04-Jun-08 Eraser 3.00 05-Jul-08 pen 4.00 06-Sep-08 pencil 5.00 07-May-08 Eraser 5.00 08-Apr-08 pen 1.00 I've modified the formula as below =SUMPRODUCT(--(A2:A8=DATEVALUE("01-04-2008")),--(A2:A8<= DATEVALUE("30-06-2008")),--(B2:B8="Pen"),C2:C8) Can please support me to find number of occurences of each product along with sum of the cost "Joel" wrote: The problem is with your data in the cells and not the formula. Make sure you change the ranges of the data to match you worksheet. The best way of debugging a formula proble is use the evaluate formula tool Menu Tools - formula auditing - evaluate formula As you evaluate the formula you should see which item causes the #value to appear. You may have to run the tool a few times until you see where the problem is located. "Shiva" wrote: Hi Joel, Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
Thanks Joel,
It's working fine now, can you please support me to find the number of Items like 'PEN' in a quarter as well. I tried with COUNTIF, but no use. "Joel" wrote: I'm getting the right answer. Why don't you change the date format like I did below. Maybe there is a problem with having that day first and then the month. =SUMPRODUCT(--(A2:A8=DATEVALUE("01-Apr-08")),--(A2:A8<= DATEVALUE("30-Jun-08")),--(B2:B8="Pen"),C2:C8) "Shiva" wrote: Joel, Thanks, It's working but shows me wrong sum value which is there in the last row Date Product Cost 02-Mar-08 pen 1.00 03-Apr-08 pencil 2.00 04-Jun-08 Eraser 3.00 05-Jul-08 pen 4.00 06-Sep-08 pencil 5.00 07-May-08 Eraser 5.00 08-Apr-08 pen 1.00 I've modified the formula as below =SUMPRODUCT(--(A2:A8=DATEVALUE("01-04-2008")),--(A2:A8<= DATEVALUE("30-06-2008")),--(B2:B8="Pen"),C2:C8) Can please support me to find number of occurences of each product along with sum of the cost "Joel" wrote: The problem is with your data in the cells and not the formula. Make sure you change the ranges of the data to match you worksheet. The best way of debugging a formula proble is use the evaluate formula tool Menu Tools - formula auditing - evaluate formula As you evaluate the formula you should see which item causes the #value to appear. You may have to run the tool a few times until you see where the problem is located. "Shiva" wrote: Hi Joel, Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
Use the same formula but remove the last term (C2:C8) and the comma before
the term. "Shiva" wrote: Thanks Joel, It's working fine now, can you please support me to find the number of Items like 'PEN' in a quarter as well. I tried with COUNTIF, but no use. "Joel" wrote: I'm getting the right answer. Why don't you change the date format like I did below. Maybe there is a problem with having that day first and then the month. =SUMPRODUCT(--(A2:A8=DATEVALUE("01-Apr-08")),--(A2:A8<= DATEVALUE("30-Jun-08")),--(B2:B8="Pen"),C2:C8) "Shiva" wrote: Joel, Thanks, It's working but shows me wrong sum value which is there in the last row Date Product Cost 02-Mar-08 pen 1.00 03-Apr-08 pencil 2.00 04-Jun-08 Eraser 3.00 05-Jul-08 pen 4.00 06-Sep-08 pencil 5.00 07-May-08 Eraser 5.00 08-Apr-08 pen 1.00 I've modified the formula as below =SUMPRODUCT(--(A2:A8=DATEVALUE("01-04-2008")),--(A2:A8<= DATEVALUE("30-06-2008")),--(B2:B8="Pen"),C2:C8) Can please support me to find number of occurences of each product along with sum of the cost "Joel" wrote: The problem is with your data in the cells and not the formula. Make sure you change the ranges of the data to match you worksheet. The best way of debugging a formula proble is use the evaluate formula tool Menu Tools - formula auditing - evaluate formula As you evaluate the formula you should see which item causes the #value to appear. You may have to run the tool a few times until you see where the problem is located. "Shiva" wrote: Hi Joel, Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarter report
Thanks Joel,
My Problem is solved. "Joel" wrote: Use the same formula but remove the last term (C2:C8) and the comma before the term. "Shiva" wrote: Thanks Joel, It's working fine now, can you please support me to find the number of Items like 'PEN' in a quarter as well. I tried with COUNTIF, but no use. "Joel" wrote: I'm getting the right answer. Why don't you change the date format like I did below. Maybe there is a problem with having that day first and then the month. =SUMPRODUCT(--(A2:A8=DATEVALUE("01-Apr-08")),--(A2:A8<= DATEVALUE("30-Jun-08")),--(B2:B8="Pen"),C2:C8) "Shiva" wrote: Joel, Thanks, It's working but shows me wrong sum value which is there in the last row Date Product Cost 02-Mar-08 pen 1.00 03-Apr-08 pencil 2.00 04-Jun-08 Eraser 3.00 05-Jul-08 pen 4.00 06-Sep-08 pencil 5.00 07-May-08 Eraser 5.00 08-Apr-08 pen 1.00 I've modified the formula as below =SUMPRODUCT(--(A2:A8=DATEVALUE("01-04-2008")),--(A2:A8<= DATEVALUE("30-06-2008")),--(B2:B8="Pen"),C2:C8) Can please support me to find number of occurences of each product along with sum of the cost "Joel" wrote: The problem is with your data in the cells and not the formula. Make sure you change the ranges of the data to match you worksheet. The best way of debugging a formula proble is use the evaluate formula tool Menu Tools - formula auditing - evaluate formula As you evaluate the formula you should see which item causes the #value to appear. You may have to run the tool a few times until you see where the problem is located. "Shiva" wrote: Hi Joel, Thanks for your support. But, I get an error when build same farmula. I get a text 'Value' in the cell. Can you help me in finding no of items for the quarter and sum of the cost for the same items for the quarter? "Shiva" wrote: Hi, Can any one help me to solve my problem. I have my business data as following Date Product Cost 2-4-08 Pen 3.0 3-6-08 Pencil 1.0 4-5-08 Eraser 0.5 Now, I want to count products individually on quarterly basis from April to July etc and find the total sum of the business for the quarter. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting missing data from one report into another report... | Excel Discussion (Misc queries) | |||
create expenditure report by quarter by category | Excel Discussion (Misc queries) | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
Header in Report Manager Report | Excel Discussion (Misc queries) | |||
First and Last Day of the Quarter | Excel Worksheet Functions |