Formula to Sum(Count)

[Crimm]

Hi, my first post here

After googling for an hour, I couldn't find an answer to my excel question - or perhaps I'm asking the wrong question. Here goes...

Attached is a snap shot of the issue. It shows two sets of data:

Example_1.xlsx

1) original data, and
2) a table created from a pivot table of original data, consolidated by customer code

In the consolidated table, I want to add two columns - by Customer
1) count of part numbers sold - only counting the part number once - as multiple transactions of the same part number may exist within each customer.
2) count of part numbers sold with existing contract price - only counting the part number once, same reason as step 1.

Is this something that can be ascertained in one formula?

Look forward to your replies.

Thank you.

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum. You have attached an image. That's not very easy to work with. We have to re-type your information before we can begin to address your problem. That will put a lot of people off completely.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

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

View Pic

[Crimm]

Hi Glenn,

Thank you for the advice. Workbook now added.

[samba_ravi]

see attached file

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Crimm]

OK, now updated.

[Glenn Kennedy]

Personally, I wouldn't use a Pivot Table. I'd use sumifs instead and then these two array formulas to deliver the end result. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Crimm]

Trying to find a way to message you zbor so I don't get done for rule 7b

[samba_ravi]

IS POST NUMBER 4 not working