I am new to pivot tables and Excel in general and I am struggling with the following problem: I have a table populated with data, and from this table I want to perform some data analysis. The Excel table structure is given below. The idea is that every row corresponds to a particular product:
Based on this table I have created a pivot table, where each row should contain information about the supplier. The first row was easy to create and it simply counts the product for the particular supplier:
Now I want to add two more calculated fields in the pivot table. The first one should sum the value in the Has Subscription? column for each supplier. This would result in summing TRUE+TRUE+TRUE+TRUE for Supp1, FALSE+FALSE+FALSE for 'Supp2' and so on. I have tried using SUMIF COUTIF and SUM(IF(..)) but I can't get it working. The second calculated field I want to insert is about counting the certificate (marked by X) column for each supplier. If we are looking at certificate 1 this should result in 2 for Supp1, 0 for Supp2 and 3 for Supp3. I thought that
=COUNTIF( 'Certificate 1 required' ,"X")
would do the job but again I end up with incorrect formula error. I think that Excel can't link the Suppliers to the columns I am interested in but I am not sure how to fix that. Or maybe I need Power Pivot for this task? I don't know so any help will be much appreciated.
