# excel calculation

Hi All,

I have this spreadsheet which I want to perform some analysis.

so I know that there are 38,695 orders out of which I have 14,064 orders for boilers which are CAT 1.

Now what I want to have is :

show how many orders contained boiler + accessory and if both appear in order were they same supplier.
Dreport.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
I see you have started to build a Pivot Table with your data and you are filtering to show only Cat 1

With that Pivot if you drag supplier to the Rows pane and place it between "Order Number" and "Product" your Summary will then show if there are multiple suppliers on each order and will show the list of products for each supplier.
Author Commented:
thanks, Rob I tried but it doesn't give me the required results.
Finance AnalystCommented:
For example, Order number 5784483 then shows:

5784483
BA/0000020
100592  Main Eco Elite 25 Combi Boiler
103849  Baxi/Main/Pott Std Horizontal
GL/0000114
101303  Heatline 24c Combi Boiler
101305  Heatline Standard Horizontal
Author Commented:
how do i get how many orders are different suppliers
Author Commented:
i want a summary or summarise numbers instead of going through whole spreadsheet
Finance AnalystCommented:
Not sure I understand the question.

Are you trying to determine how many suppliers per order. So for the above order that I mentioned the result would be 2 (BA/0000020 and GL/0000114).

Another example, order 7354436 has 3 suppliers (BA/0000020 and CA/0000121 and WO/0000050)

Is that what you require?
Author Commented:
yes please, all I need is the number of orders with different suppliers under one order.
Finance AnalystCommented:
See attached.

I have put the pivot back to having the Supplier as a column. Alongside the pivot I have then put a formula checking the total row for each order and counting the number of values under the supplier columns, multiple suppliers will show result of 2 or more.

You can then filter on this column to show the Order Number and number of Suppliers.
Dreport--2-.xlsx

Experts Exchange Solution brought to you by