+ Reply to Thread
Results 1 to 19 of 19

Get count of items based on 4 variable conditions

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

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

  2. #2
    Registered User
    Join Date
    06-01-2013
    Location
    Northampton, England
    MS-Off Ver
    Excel 2010
    Posts
    17

    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. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    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()
    Last edited by Richard Buttrey; 12-04-2013 at 11:22 AM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,714

    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.
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    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. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,714

    Re: Get count of items based on 4 variable conditions

    if I'm not mistaken mine gives you that.

  7. #7
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Get count of items based on 4 variable conditions

    No mate, it's not.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,714

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

  9. #9
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    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. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,714

    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?
    Last edited by Sam Capricci; 12-04-2013 at 01:36 PM.

  11. #11
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    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. #12
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    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. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Get count of items based on 4 variable conditions

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

  14. #14
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Get count of items based on 4 variable conditions

    PT can't give me the desired results.

  15. #15
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Get count of items based on 4 variable conditions

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

  16. #16
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Get count of items based on 4 variable conditions

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

  17. #17
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Get count of items based on 4 variable conditions

    Quote Originally Posted by m2som View Post
    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. #18
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Get count of items based on 4 variable conditions

    could you please do it for me and send thru?

  19. #19
    Registered User
    Join Date
    03-23-2013
    Location
    egypt
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Get count of items based on 4 variable conditions

    Any one gents can help me here?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. sum + count if based on 3 conditions
    By Armitage2k in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-06-2013, 06:50 AM
  2. [SOLVED] Formula to get the count of items that satisfy two conditions
    By Excel Dumbo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-23-2013, 04:32 PM
  3. Sort based on multiple variable conditions
    By tstoney in forum Excel General
    Replies: 8
    Last Post: 03-14-2012, 06:37 PM
  4. [SOLVED] count based on two conditions
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2006, 05:10 PM
  5. How do I count based on two conditions?
    By Mark G in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-31-2006, 12:10 AM

Bookmarks

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