I need to calculate the number of different orders that have been made based on the supplier and the date. MY Excel file has the supplier name in the collumn A, and the order date in the collumn B. Each date change for a specific supplier is one order. I need to calculate the total of different order.
Thanks for helping,
Simon
Post about 20 rows worth of sample data and tell us what result you expect.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi,
Here is a sample excel file.
https://drive.google.com/file/d/0B04...ew?usp=sharing
I don't see anywhere that shows us what results you expect?
We made orders to suppliers (collumn A). An order can have multiple different items (Collumn C), ordered on the same date (Collumn B). I want to know how many different orders we made during the year. I guess i need a sub-total for each supplier (A) based on the total of different date (B). For example, A & C AMERICAN CHEMICALS LTEE has 2 different orders, ABRATECH INC. has 8 different orders (different date), etc. I need the total.
Thanks...
Try this array formula**:
=SUM(IF(FREQUENCY(MATCH(A2:A112&B2:B112,A2:A112&B2:B112,0),ROW(A2:A112)-ROW(A2)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Based on your sample this returns 63.
Re: Sub total based on date change and supplier
Perfect! Thanks Tony, exactly was I was looking for, it will save time...
Regards,
Simon
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks