Counting unique deals with multiple criteria

[confused in Frankfurt]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deal ID[/TD]
[TD]Sales Rep[/TD]
[TD]Quarter[/TD]
[TD]Value[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl258, width: 80, align: left"]0087060[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fred Blogs[/TD]
[TD]4[/TD]
[TD]1.000[/TD]
[TD]bread[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl258, width: 80, align: left"]0087060[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fred Blogs[/TD]
[TD]4[/TD]
[TD]20.000[/TD]
[TD]butter[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl258, width: 80, align: left"]0087061[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fred Blogs[/TD]
[TD]3[/TD]
[TD]45.000[/TD]
[TD]bread[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl258, width: 80, align: left"]0087763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John Smith[/TD]
[TD]4[/TD]
[TD]30.000[/TD]
[TD]bread[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl258, width: 80, align: left"]0087763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John Smith[/TD]
[TD]4[/TD]
[TD]45.000[/TD]
[TD]butter[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl258, width: 80, align: left"]0088163[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fred Blogs[/TD]
[TD]4[/TD]
[TD]50.000[/TD]
[TD]bread[/TD]
[/TR]
</tbody>[/TABLE]
Hi I have a table where each deal is split into products. I would like to count the number of deals per person restricting to Q4 and >=50.000. Preferably I would like to do this without creating an extra table reducing out the duplicates by ignoring the product.
Result should be : Fred Blogs 1 and John Smith 1 (30+45)

If I am ignoring the duplicates and counting just every thing I am using the formula
=countifs(Range1;...)

but this is either giving me too many deals to use for calculating average deal size or too few because the split deal value is below the benchmark set.
Regards
Sarah

 

[Aladin Akyurek]

It's not clear what the output should be. Also, there is just one entry for >=50,000...

 

[MichaelB1]

Hi! If you're happy to have a new column, you could use the following (assuming deal ID is in col A and value is in Col D)

=IF(A2=A3,"",IF(SUMIF($A$2:A3,MAX($A$2:A3),$D$2:D3)<50000,"",SUMIF($A$2:A3,MAX($A$2:A3),$D$2:D3)))

Then put a count at the bottom of this new column. This formula should mean that if the sum of the value for a given deal ID is < 50,000, it should return ""; if not, it returns the sum of the deal value.

Hope this helps - let me know if this needs refining further.

 

[confused in Frankfurt]

Hi there,
the formula didn't return anything. Had to adjust to the German formula but pulled up just blanks. Not even an error.
=WENN(A2=A3;"";WENN(SUMMEWENN($A$2:A3;MAX($A$2:A3);$I$2:I3)<50000;"";SUMMEWENN($A$2:A3;MAX($A$2:A3);$I$2:I3)))

What I forgot to mention is that the deals are not just split into 1 or two there can be multiple products sold.

I am assuming that I would then write the end formula checking for the arguments based on the new column - if it returned an answer.

 

[confused in Frankfurt]

the output should be : Fred Blogs 1 and John Smith 1 (the sum of the total deal30+45)

 

[Aladin Akyurek]

the output should be : Fred Blogs 1 and John Smith 1 (the sum of the total deal30+45)

Given the criteria Quarter = 4 and Value >= 55000, how do we manage those results above?

 

[confused in Frankfurt]

Fred Blogs Deal 0088163 50.000 count 1
John Smith sum of deal 0088763 (45+30=75) count 1