Count and Sum together problem.

[fahadferoz]

Please help me with the file. I have already uploaded the file.

I need to use sum and count formula together with an if option. Please see the uploaded file to better understand the problem.

Please help me!

 

[bosco_yip]

Maybe,

L5, copy down :

=COUNTIFS(D:D,K5,I:I,1)

Regards

 

[fahadferoz]

Does not work.

The formula does not show the summation of all outlets (which all the the summation of all 1 if the value entered to the cell is 1 or more).

I uploaded using your formula. Please have a look. It did not work

 

[bosco_yip]

Does not work

Give your expected results.

Regards

 

[fahadferoz]

Using Countif formula we will get Ashish has a total of 18 outlets. However, if you look at column I, then you will see Ashish was not able create memo to 6 outlets. So, M5 cell needs to show 12 instead of 18.

Please help me solve it.

 

[bosco_yip]

......... So, M5 cell needs to show 12 instead of 18....

Then, M5 :

=COUNTIFS(D:D,L5,I:I,">0")

Or,

=COUNTIFS(D$3:D$62,$L5,INDEX(I$3:J$62,0,MATCH(M$4,I$2:J$2,0)),">0")

Copy down

Regards

 

[fahadferoz]

Many thanks for your kindness.