calcutation

Y

Yvonne Ng

Board Regular
Joined
Nov 5, 2013
Messages
67
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?　
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Hi Yvonne,

Add a helper column after your date and put in the formula =TEXT(B1,"mmm") if you are just interested in the month or =TEXT(B1,"mmm-yy") if you want your data in month and years.

You can then use SUMIF and COUNTIF to get your data on sheet 2.

hope this helps,

FarmerScott
 
Upvote 0
In Worksheet 1
Type Of fruits date of order Amount (s$)
Fruit A 1-Apr-13 $50
Fruit B 13-April $ 40
Fruit A 3-Mar-13 $ 40
Fruit C 1-Mar-13 $ 40
Fruit A 30-Mar-13 $ 10

In Worksheet 2 ,

in march
type of fruit total no of orders Total amount
Fruit A 2 $50
Fruit C 1 $40

In april
type of fruit total no of orders Total amount

Fruit A 1-Apr-13 $50
Fruit B 13-April $ 40



In march, Fruit B table will not appear in march since there's no order in march
In april, Fruit C will Not apprear in april's table since no order in april
 
Last edited:
Upvote 0
Sheet1, A:C, houses the data.


TypeDateAmount
Fruit A1-Apr-13$50
Fruit B13-Apr$40
Fruit A3-Mar-13$40
Fruit C1-Mar-13$40
Fruit A30-Mar-13$10

<COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4494" width=126><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2645" width=74><TBODY>
</TBODY>

Select A2:A6, go to the Name Box on the Formula Bar, and enter Type.
Select B2:B6, go to the Name Box on the Formula Bar, and enter Date.
Select C2:C6, go to the Name Box on the Formula Bar, and enter Amount.

Sheet2, A:B, houses the processing for March 2013...

1-Mar-13Total
TypeCountAmount
Fruit A250
Fruit C140

<COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

aa

In A1 enter: 1-Mar-2013 (Custom format this as Mar-13 if so desired.)

A3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(Type,SMALL(IF(FREQUENCY(IF(Type<>"",
  IF(Date-DAY(Date)+1=$A$1-DAY($A$1)+1,
  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:
Rich (BB code): 
=IF($A3="","",SUMPRODUCT(--(Type=$A3),--(Date-DAY(Date)+1=$A$1-DAY($A$1)+1)))

C3, just enter and copy down:
Rich (BB code): 
=IF($A3="","",SUMPRODUCT(Amount,--(Type=$A3),--(Date-DAY(Date)+1=$A$1-DAY($A$1)+1)))

The set up for April 2013 follows the same logic. I trust it won't be too difficult to implement that in the same way.
 
Upvote 0
My A3 give me a blank cell... is it I miss out something?
Can i customize my A1 in sheet 2 to be just only mar, apr, jan, etc?
 
Last edited:
Upvote 0
Yvonne Ng said:
My A3 give me a blank cell... is it I miss out something?
Can i customize my A1 in sheet 2 to be just only mar, apr, jan, etc?
Click to expand...

Please try to follow the instructions...

https://dl.dropboxusercontent.com/u/65698317/aaCalculation%20Yvonne%20Ng.xlsx


Why would it matter that much that A1 is exactly mar or 1-Mar-13, custom-formatted as mmm?

P.S. In your earlier thread we had this issue of mar vs 1-Mar-13. If you insist on a literal mar, you might refer to that thread. Or, otherwise, we can adapt the current formulas to the literal mar demand.
 
Upvote 0
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?

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

Please advise.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
FILTER Formula: Simultaneous AND and OR Conditions?
2
Replies
10
Views
500
Peter_SSs
Peter_SSs
M
  • Question
Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner
Replies
7
Views
134
maeyks
M
V
  • Question
VBA copy data from workbook to another based on criteria and copy method
Replies
0
Views
335
VfTom
V
M
  • Solved
Small help- in excel
2 3 4
Replies
30
Views
789
madhanji
M
T
  • Solved
Match with multiple columns
2
Replies
14
Views
424
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,622
Messages
6,120,572
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top