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
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
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.
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.
For the specific month that exist in column B....I copied it in and it seems to give the count for each individual piece of equipment.
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
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
Hoping that i got your goal,i used a helper & hidden column with this formula.
Formula:Please Login or Register to view this content.
Then using this one, we get your results.
Formula:Please Login or Register to view this content.
Correct?
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.
Ok. In C2 of my example sheet use this formula and copy down.
Formula:Please Login or Register to view this content.
Thank so much. Works perfect!!
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?
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
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks