Extracting data from salesforce or qlikview may not give the information I needed, it already happened that I needed to use a formula to know a certain amount of sales done by members of my team and how many sales did he do to get this amount. Also, it is useful to know for instance how many people bought a product and how much in total for this specific product.

When I use the formula ?

To know the full amount of sales and to know how many sales done but it can be used for something else.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=SUMPRODUCT()

=COUNTIF()

=LEFT()

This formula will tell me how much the member A sold during this year:

=SUMPRODUCT((C2:C10)*(A2:A10="member A"))

This formula will tell me how many the member A sold during this year:

=COUNTIF(A:A,"member A")

So to resume, I know that the member A did only 3 sales for a value of 38417.70 euros during this year.

The SUMPRODUCT can combine multiple criterias, for instance, I want to know how much the member A did for the client 1 so the formula would be:

=SUMPRODUCT((C2:C10)*(A2:A10="member A")*(B2:B10="client 1"))

For the other formula, I have to use COUNTIFS in plural, so for the same example:

=COUNTIFS(A:A,"member A",B:B,"client 1")

One of the good thing with COUNTIFS, I can use a wildcard, I mean an asterisk “*”, so if I want to calculate only the team starting with “mem”, it will be:

=COUNTIFS(A:A,"mem*",B:B,"client 1")

But unfortunately, for SUMPRODUCT, it doesn’t work. As a workaround, I will use the LEFT function:

=SUMPRODUCT((C2:C10)*(LEFT(A2:A10,3)="mem")*(B2:B10="client 1"))

NOTE: 3 is the number of the first letters starting from the left. If I want from the right, as you may guess, just use the RIGHT function.