saggrigate data on latest date and upon conditions meet

khalidnisar

New Member
Joined
May 4, 2011
Messages
8
I have a sheet with following data

Row 2:
Level, Slab Area, status, casting date.
P2 , 212 , casted , 2-5-11
P3 . 486 ,
P2 ,500 , casted , 2-5-11
P2 , 100 , casted , 5-1-11
P1 , 200 , casted , 9-2-11
GF , 400 , casted , 7-3-11

I need the above table to reflect the most recent casted date for a particular slab and its area information infront of it. Further if there are two or more slabs casted in that particular date it should be able to add those.

Level, slab area , latest casting date
P3 , formula??? , formula??
P2 , formula??? , formula??
P1 , formula??? , formula??
GF , formula??? , formula??


I don't know if i have make my question clear, any help will be highly appriciated and thanks in advance.
regards
Khalid
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
khalidnisar, your question is not clear, can you please give some more explanation to understand same.
 

khalidnisar

New Member
Joined
May 4, 2011
Messages
8
I have a big file of above data, for which i want to Auto-segregate the data based on the latest casting date (of each slab level "P2,P1,GF..") into another small table which is (Table-2) given above.
Table -2 will reflect the latest casting date along with the Area information in front of each particular slab
i need help to compile a code of formulla for that.
regards
 

khalidnisar

New Member
Joined
May 4, 2011
Messages
8
I have a sheet with following data

Row 2:
Level, Slab Area, status, casting date.
P2 , 212 , casted , 2-5-11
P3 . 486 ,
P2 ,500 , casted , 2-5-11
P2 , 100 , casted , 5-1-11
P1 , 200 , casted , 9-2-11
GF , 400 , casted , 7-3-11

I need the above table to reflect the most recent casted date for a particular slab and its area information infront of it. Further if there are two or more slabs casted in that particular date it should be able to add those.

Level, slab area , latest casting date
P3 , formula??? , formula??
P2 , formula??? , formula??
P1 , formula??? , formula??
GF , formula??? , formula??


I don't know if i have make my question clear, any help will be highly appriciated and thanks in advance.
regards
Khalid

I did it:
For latest casting date filtration
condition:
B8-B284 is my range which is equals to "P3"
u8-u284 is my range which is equals to "CAST"
Date cell:
v8-v284 is my range which is equals to date column

=SUMPRODUCT(MAX((B8:B284="P3")*(U8:U284="CAST")*(V8:V284)))

For sum of areas on specific date or between particular date
I8-I284 is my range where the area is
v8-v284 is my range which is equals to date range column
C290 is cell where the date is
B8-B284 is my range which is equals to "P3"

=SUMIFS(I9:I281,V9:V281,"="&C290,B9:B281,"P3")

H288 & I288 is my date range cells
=SUMIFS(I9:I281,V9:V281,">="&H288,V9:V281,"<="&I288,B9:B281,"P3")

hope that will help
regards
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

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
Top