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

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

2. ## 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)))

3. ## 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. ## 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. ## 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. ## 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:
`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?

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

8. ## 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:
`Please Login or Register  to view this content.`

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

Thank so much. Works perfect!!

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

Edit:

=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!

12. ## 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?

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