calcutation

[Yvonne Ng]

Hi,
any one can help?

I've a list of different fruits, fruit A, fruit B, fruit C in worksheet 1 coloum A and with order date in Coloum B in dd-mm-yy (month in character), and coloum c, amount purchase for each order.
I need a formula in worksheet 2 that i can gather the different type of information, example, in coloum 1, the type of fruits I order for tat particular month, Mar, example. and also im coloum 2 how many times i order within the month of Mar. and coloum 3, total amount of fruits for tat particular type i order for the same month

however, example, in april, i did not order fruit A, but order fruit b, so in my april summary, it will not shown the fruit A apprear in my coloum 1 in worksheet 2 since i no place order in april

is there a way to do it?　

 

[Aladin Akyurek]

Thank you, Its Works.
But when i insert Fruit D in worksheet 1 into the month of Mar, Work sheet 2 did not capture this data.
why is it so?

In which row did you add The Fruit D record?

As I'll have different type of fruits order for different month, thus Worksheet 1 coloum 1 is also a variable

Please advise.

The formula system processes the fruits in column 1, i.e., column A. Am I missing something?

 

[Yvonne Ng]

Hi,
I've add Fruit D in A7, 1-mar-13 in Colum B, and $ 30 in Coloum C in worksheet 1, but worksheet 2 does not capture the data.

I don't quite understand th formula, may I know which part of the formular is link to Worksheet 1 Coloum A, whch part of the formular is link to Worksheet 1 Coloum B, etc

 

[Aladin Akyurek]

Hi,
I've add Fruit D in A7, 1-mar-13 in Colum B, and $ 30 in Coloum C in worksheet 1, but worksheet 2 does not capture the data.

The original data consists of 6 rows. You have added the Fruit D record outside that area. So, no wonder that the formulas do not see that record.

I don't quite understand th formula, may I know which part of the formular is link to Worksheet 1 Coloum A, whch part of the formular is link to Worksheet 1 Coloum B, etc

If you look at my reply, you'll see that Type reference Sheet1!A2:A6, Date Sheet1!B2:B6, and Amount Sheet1!C2:C6.

If we make the definitions of Type, Date, and Amount dynamic, we can freely add new records. The formulas will include such immediately.

Define Lrow as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)

Type as:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

Date as:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

Amount as:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

We keep formulas as is, posted earlier.

See:

https://dl.dropboxusercontent.com/u/65698317/aaCalculation with dynamic definitions Yvonne Ng.xlsx

Have a look at the dynamic definitions via Formulas | Name Manager.

 

[Yvonne Ng]

may i know where can i locate the Lrow formula? how to make definations of the type, date and amount?

 

[Aladin Akyurek]

may i know where can i locate the Lrow formula? how to make definations of the type, date and amount?

Go to Formulas and then to Name Manager.

 

[Yvonne Ng]

Thank you so much, Its finally works, appreciates if you could advise the formula to change to just only month instead if dd-mm-yy FOR wooksheet 2.
i try to change but not sure how

 

[Aladin Akyurek]

Thank you so much, Its finally works, appreciates if you could advise the formula to change to just only month instead if dd-mm-yy FOR wooksheet 2.
i try to change but not sure how

Sheet2

A1: Mar (a literal month name)

A3, control+shift+enter and copy down:

=IFERROR(INDEX(Type,SMALL(IF(FREQUENCY(IF(Type<>"",IF(Date-DAY(Date)+1=(1&$A$1)+0,
  MATCH(Type,Type,0))),ROW(Type)-ROW(INDEX(Type,1,1))+1),ROW(Type)-ROW(INDEX(Type,1,1))+1),
  ROWS(A$3:A3))),"")

B3, just enter and copy down:

=IF($A3="","",SUMPRODUCT(--(Type=$A3),--(Date-DAY(Date)+1=(1&$A$1)+0)))

C3, just enter and copy down:

=IF($A3="","",SUMPRODUCT(Amount,--(Type=$A3),--(Date-DAY(Date)+1=(1&$A$1)+0)))

 

[Teeroy]

I would have thought that this problem lent itself to a PivotTable rather than a formulaic solution.

 

[Yvonne Ng]

Thank you so much

 

[Aladin Akyurek]

Thank you so much

You are welcome. Thanks for providing feedback.