+ Reply to Thread
Results 1 to 12 of 12

Need a column that will specify how many pieces of epuipment are running per month, help!

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Need a column that will specify how many pieces of epuipment are running per month, help!

    Hi, I am trying to create column C which will give me number of equipment per month, there are multiple days of data each month for each piece of equipment. I only want a count of each unique equipment for each month.


    Capture.JPG
    Attached Images Attached Images

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Pictures are nice but don't help to work on it.

    As an idea, in C2 and copy down.

    =SUMPRODUCT(($A$2:$A$100=A2)*(MONTH($B$2:$B$100)=MONTH(B2)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Is there any way to tweak that formula to give the total number of equipment for the whole month? I copied it in and it seems to give the count for each individual piece of equipment. I want a unique count totaled for the whole month.

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    ...I copied it in and it seems to give the count for each individual piece of equipment.
    For the specific month that exist in column B.

    If this is not what you want.

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Sorry if I did not upload correctly, new to using this.

    Hopefully the document provides a clearer explanation of what im trying to do. Example- even if "equipment A" shows up three times in one month, it should only be counted as one piece of equipment for the whole month.



    Sample Workbook.xlsx

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Hoping that i got your goal,i used a helper & hidden column with this formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then using this one, we get your results.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Correct?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Still not quite what im looking for. I need column C to add up each unique piece of equipment for the whole month. So if there are three unique pieces running for a month, column c will show the number 3 for the whole month. If the same name shows up more than once in the same month it should only be counted once to the whole total.
    Attached Files Attached Files

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Ok. In C2 of my example sheet use this formula and copy down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Thank so much. Works perfect!!

  10. #10
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Thanks again, I have one more question. If I want to use these formulas on a larger data set, how would i go about changing the formula if i had like 200 rows?

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Edit:

    Just show your last post

    =SUMPRODUCT((MONTH($B$2:$B$200)=MONTH(B2))*($D$2:$D$200=1)*($D$2:$D$200))

    Modify also using samw way the formula in hidden column!
    Last edited by Fotis1991; 10-09-2013 at 09:48 AM. Reason: Edit

  12. #12
    Registered User
    Join Date
    01-14-2013
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Need a column that will specify how many pieces of epuipment are running per month, he

    Hi again, I need some more help on the same formula. How does it need to be changed if there are different years in the data? Also the data im using will be automatically updated from a server, the rows will continue to increase, is there a way to automatically make the formula pick up the new rows that are added?

+ 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. Splitting data in a column up into pieces of 20 at a time
    By vzc8 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2011, 10:57 AM
  2. Excel 2007 : Running Month to Month totals for end of year
    By Josephb1976 in forum Excel General
    Replies: 0
    Last Post: 07-29-2011, 04:54 PM
  3. Filter with Multiple Pieces of Data in One Column
    By Jaricketts in forum Excel General
    Replies: 1
    Last Post: 03-01-2010, 05:31 PM
  4. Running Percentage Totals for Month
    By emitchell88 in forum Excel General
    Replies: 1
    Last Post: 03-01-2009, 05:55 PM
  5. delete whole column if 1:20 contains any one of 5 pieces of text
    By jamiepullen in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2008, 06:12 PM

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