CODEWITHSUNDEEP
excelpivot-table
Data Science
Data Science

Reputation: 79

Conditions in Excel for calculations?

I currently have the following dataset:

Dataset Image below

What i would like to calculate is the following:

How can i find a way of extracting the number of times a company has only used 1 product, 2products, 3 products etc

The values in the rows are just the counts (so the amount of times a company used that product e.g. 1 means they used product once, twice means product 2 times etc)

So in the dataset image example

Company 1 has only used 1 product, i would like to apply this to the entire dataset, where only 1 product has been used. This is the same for company 2 as only 1 product was used but 3 times, so an output would say something like "2" - which corresponds to the amount of companies that have only used 1 product

And for example where there is more than one product used such as company 3, i would like to have it read "3" the amount of companies that used 3 products

etc

Any ideas

Please see my propsed outcome below:

Thanks

enter image description here

Upvotes: 1

Views: 46

Answers (1)

Jeffrey Bird
Jeffrey Bird

Reputation: 31

Add a new column PRODUCT_COUNT with the following formula, and drag down.

=COUNTIF(B2:I2,”>0”) to count the number of products utilized by each company more than zero times.

In your example you have 7 companies. So in your summary table, you could do another COUNTIF like so:

  • =COUNTIF($H$2:$H$8,1) to count how many companies used exactly 1 product.

  • =COUNTIF($H$2:$H$8,2) to count how many companies used exactly 2 products.

  • =COUNTIF($H$2:$H$8,”>=3”) to count how many companies used 3 or more products.

If the number of companies changes, you would need to adjust the size of the range. A named range in the PRODUCT_COUNT column may be a good idea.

=COUNTIF(PRODUCT_COUNT,”>=3”)

Upvotes: 3

Related Questions