quarter report

[Shiva]

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.

 

[Joel]

=SumProduct(--(A1:A100 >= datevalue("4-1-08")),--(A1:A100 <=
datevalue("6-30-08")),--(B1:B100="Pen"),C1:C100)

 

[Shiva]

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?

 

[Joel]

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]

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]

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]

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]

Use the same formula but remove the last term (C2:C8) and the comma before
the term.

 

[Shiva]

Thanks Joel,

My Problem is solved.

Use the same formula but remove the last term (C2:C8) and the comma before
the term.