Help: Count and Sum with multiple criteria

[Abhi Agarwal]

Hi!

I have a data list (sample attached) where I want to calculate if the category in column F is mentioned in category list Column H

1. How many unique document numbers for that category
2. Sum of the values (Column D) for that category
3. The above needs to be calculated plant wise (Column E)

For e.g. for category ACOUSTIC the output would be

Category Accoustic
Count of unique document number (Plant A) - 2
Count of unique document number (Plant B) - 2
Value (Plant A) - 11230
Value (Plant B) - 4260


Please help

Abhi

 

[GraH - Guido]

Hi, you could do it with a simple pivot table. Count distinct is available when loaded to a data model (Excel 2013 and up).
I also converted your data range also to a table. Adding new data to this table -> refresh pivot and calculations are done.

 

[Abhi Agarwal]

Thanks Grah!

But if you see my excel sheet I want to calculate the output as illustrated in the attached excel only for the category in Column F if only it is present in category list Column H.

I have attached the sample excel sheet again with more values for clarity

Thanks
Abhi

 

[GraH - Guido]

Hi,
I've added a simple "InList" column and filtered the pivot.

 

[vletm]

Abhi Agarwal
... or without Pivot