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?　

 

[Yvonne Ng]

Hi, Now I would like to change my formula by removing the month, show only the type of fruits and amount regardless of month. Can any one help?

 

[Yvonne Ng]

Hi, I would now want only the type of fruits and total appear on the second sheet regardless of month. How should the formula be? Appreciates your advise. Thank you. Data as follows :

Type	Amount
Fruit A	$50
Fruit B	$40
Fruit A	$40
Fruit C	$40
Fruit A	$10
Fruit D	$45
Result would be : Type Amount Fruit A 100 Fruit B 40 Fruit C 40 Fruit D 45 <tbody> </tbody><colgroup><col><col></colgroup>

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Aladin Akyurek]

Hi, I would now want only the type of fruits and total appear on the second sheet regardless of month. How should the formula be? Appreciates your advise. Thank you. Data as follows :

Type	Amount
Fruit A	$50
Fruit B	$40
Fruit A	$40
Fruit C	$40
Fruit A	$10
Fruit D	$45
Result would be : Type Amount Fruit A 100 Fruit B 40 Fruit C 40 Fruit D 45 <tbody> </tbody>

<tbody>
</tbody>

A:C in Sheet1 houses the data, with Type located in A and Amount in C.

A:B in Sheet2 houses the processing, with Type in A and Amount in B.

If the types are already given in A, a SumIf formula will do:

B2, just enter and copy down:

=SUMIF(Sheet1!A:A,$A2,Sheet1!C:C)

If you want to use of the dynamic named ranges we had alredy define, this formula becomes:

=SUMIF(Type,$A2,Amount)


Finally, if you want to create the types automatically in A as we have done previously but this time without the date condition, please advise.

 

[Yvonne Ng]

i'll have a variable of fruits for the table, is there a formula to consolidate in sheet 2 without repeating while I still able to calculate the total amount of the variable? please give example. Thank you.

 

[Aladin Akyurek]

i'll have a variable of fruits for the table, is there a formula to consolidate in sheet 2 without repeating while I still able to calculate the total amount of the variable? please give example. Thank you.

Am I allowed to use the definitions we already have discussed, that is, Type, Amount, etc?