Hi Guys,
Please help me on this, I have attached a sample file for explanation, I have 2 sheets, the first one is rods register I put in it,
1. date,
2. rod type,
3. location,
4. status.
what I need in the second sheet " Summary" I to get report about all rods count related to location , status, and number of rods for a certain month which I can choose form cell D3.
Hi
Have you considered using the AutoFilter option (Data, Filter)? Works well for me with contiguous data. You can filter on Current Location, Previous Location or any other column. To sum Number of Rods column use Subtotal() function.
Regards
Alan Clubb
Hi,
Why not just use a Pivot Table based on your Rods Register sheet then you avoid functions altogether.
However if you do want to use functions and avoid some messy constructs, you'll need to change your summary sheet so that you hold the location as a separate column and record it on every row, rather than in a single cell above each block. Then you can use SUMIFS()
Last edited by Richard Buttrey; 12-04-2013 at 11:22 AM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Ok, this is how i'd do it, a little sloppy because i added helper columns to do the calculations but it works...
You'll see what i added in the rods register sheet columns K through N.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
what I meant to say that I need cumulative result, which means,
if I have 1 rod at November with 100% status, and I choose November will give me count 1 rod at 100 %, then at December its status been changed from 100 % to 75 %, so if I choose December, It gives 0 100% and 1 at 75%.
if I'm not mistaken mine gives you that.
No mate, it's not.
Ok, as i understood your request, you want to select a month in cell C3 of the summary worksheet and have the numbers reflected update for the rows in col A.
So it appears more info was needed, look at this to see if it is closer...
No, It's not cumulative, which means I choose December, data of November + December will appear, Also If any changes happen in rods status it should appear as well, hopefully I explained it well.
Ok, so for clarification, where you write it isn't cumulative you are referring to my solution and you want it to be cumulative. And if cumulative, how cumulative do you want it to be? 2 months, 5 months or going back to the beginning of the database?
EDIT: from your first post I thought you needed counts, but do you really need the corresponding number for each rod type? so instead of a count of november rods you need a sum of the values in col I of the rods register?
Last edited by Sam Capricci; 12-04-2013 at 01:36 PM.
Yes you got me Now,sorry for not being clear before, I need sum of rods form the begining to the month I choose,Also i need any changes on rods status to be effective.
Like if I have 2 rods 100 % and 1 75% at november and I add 2 extra rods 100% and change 1 rod from 100% to 75% in December, so the results should be as the following,
1. choose November:
2 rods 100%,
1 rod 75%,
2. choose December:
3 rods 100%,
2 rod 75%,
Still can't help thinking a PT is the better way to proceed
PT can't give me the desired results.
Why not? Have you tried. I don't see any insurmountable problemsOriginally Posted by m2som
I tried but I couldn't, because I need cumulative result not monthly.
PT are perfectly capable of summing fields across any time periods you want. A cumulative is merely a whole field sum.
could you please do it for me and send thru?
Any one gents can help me here?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks