# Get count of items based on 4 variable conditions

1. ## Get count of items based on 4 variable conditions

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.

2. ## Re: Get count of items based on 4 variable conditions

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

3. ## Re: Get count of items based on 4 variable conditions

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()

4. ## Re: Get count of items based on 4 variable conditions

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.

5. ## Re: Get count of items based on 4 variable conditions

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%.

6. ## Re: Get count of items based on 4 variable conditions

if I'm not mistaken mine gives you that.

7. ## Re: Get count of items based on 4 variable conditions

No mate, it's not.

8. ## Re: Get count of items based on 4 variable conditions

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...

9. ## Re: Get count of items based on 4 variable conditions

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.

10. ## Re: Get count of items based on 4 variable conditions

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?

11. ## Re: Get count of items based on 4 variable conditions

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.

12. ## Re: Get count of items based on 4 variable conditions

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%,

13. ## Re: Get count of items based on 4 variable conditions

Still can't help thinking a PT is the better way to proceed

14. ## Re: Get count of items based on 4 variable conditions

PT can't give me the desired results.

15. ## Re: Get count of items based on 4 variable conditions

Originally Posted by m2som
PT can't give me the desired results.
Why not? Have you tried. I don't see any insurmountable problems

16. ## Re: Get count of items based on 4 variable conditions

I tried but I couldn't, because I need cumulative result not monthly.

17. ## Re: Get count of items based on 4 variable conditions

Originally 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.

18. ## Re: Get count of items based on 4 variable conditions

could you please do it for me and send thru?

19. ## Re: Get count of items based on 4 variable conditions

Any one gents can help me here?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1